SQL Functions

SQL functions are functions that return one or more data points when they are called. They can be called within the queries. There multiple types of functions:

Scalar Functions: Returning one value. Like Len() function.

In-line Table-valued Functions (ITVF): You can call as tables.

CREATE FUNCTION s1.F1(int @b1)
RETURNS TABLE AS 
RETURN SELECT a FROM T WHERE T.b = @b1
GO

SELECT * FROM s1.F1(2);

Multi-statement Table-valued functions (MSTVF): They can return tables but they allow more manipulations like insert, update, delete, etc.

CREATE FUNCTION MyNS.GetLastShipped(@ID INT)
RETURNS @CustomerOrder TABLE
(ID INT, Qty INT)
AS
BEGIN
Insert .. SELECT .. where ID = @ID
End
GO

Functions accept parameters.

Leave a Reply

Your email address will not be published. Required fields are marked *