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

/****** TABLE-VALUED Function******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[Split]  

(  

 @RowData nvarchar(2000),  

 @SplitOn nvarchar(5)  

)    

RETURNS @RtnValue table   

(  

 Id int identity(1,1),  

 Data nvarchar(100)  

)   

AS    

BEGIN   

 --Declare @Cnt int  

 --Set @Cnt = 1  

 While (Charindex(@SplitOn,@RowData)>0)  

 Begin  

  Insert Into @RtnValue (data)  

  Select   

   Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))  

  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))  

  --Set @Cnt = @Cnt + 1  

 End  

 Insert Into @RtnValue (data)  

 Select Data = ltrim(rtrim(@RowData))  

 Return  

END 

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

select SUBSTRING('Hello', 2, 3) as subs;  //op:    ell

select * from dbo.Split('997628946,8497648946',',');   // id    Data 

 // 1 997628946, 

  // 2   8497648946

//==============================