User Defined Functions

SCALAR VALUED FUNCTION

CREATE FUNCTION ufnGetFullName(@firstname NVARCHAR(100),@lastname NVARCHAR(100))

RETURNS NVARCHAR(201)

AS

BEGIN

RETURN @firstname+' '+@lastname

END

SELECT dbo.ufnGetFullName('FirstName,'LastName');

result: FirstName LastName

//-----------------------------------------------

TABLE VALUED FUNCTION

CREATE FUNCTION dbo.ufnGetProductsAbovePrice(@Minprice INT)

RETURNS TABLE

AS

RETURN

(

SELECT ProductID, [Name], ListPrice 

FROM Production.Product

WHERE ListPrice>@Minprice

)

SELECT * FROM dbo.ufnGetProductsAbovePrice(3000)

MULTILINE TABLE VALUED FUNCTION

CREATE FUNCTION dbo.ufnGetTopOrdersByYear

(

@OrderYear INT = NULL,

@TopN INT = 10

)

RETURNS @OrderList TABLE

(

SalesOrderID INT,

OrderDate DATE,

CustomerID INT,

TotalDue MONEY

)

AS

BEGIN

IF @OrderYear IS NULL

BEGIN

SET @OrderYear = YEAR(GETDATE());

END

INSERT INTO @OrderList

SELECT TOP (@TopN)

SalesOrderID,

OrderDate,

CustomerID,

TotalDue

FROM Sales.SalesOrderHeader

WHERE YEAR(OrderDate) = @OrderYear

ORDER BY TotalDue DESC;

RETURN;

END