datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update

SQL Server Training Scripts

User-Defined Function Architecture

The following Microsoft SQL Server T-SQL functions are examples for scalar and table-valued UDF design:

   1. dbo.fnSplitStringListXML -- applying xml xquery to split

   2. dbo.fnSplitCSV -- applying charindex on number list

   3. dbo.fnSplitCSVxml -- applying xml operations on number list

   4. dbo.fnSplitStringList -- applying charindex on string list

   5. dbo.tvfGetSalesInfoByProductColor -- like a view with parameter

   6. dbo.fnSplitDelimitedStringList -- split delimited list

-- Split a comma-delimited string list with XML method

 

-- Table-valued user-defined function - TVF - SQL Server 2005/2008

-- Method uses XML xquery to split string list

CREATE FUNCTION dbo.fnSplitStringListXML (

      @StringList VARCHAR(MAX),

      @Delimiter CHAR(1))

 

RETURNS @TableList TABLE(ID int identity(1,1), StringLiteral VARCHAR(128))

BEGIN

      IF @StringList = '' RETURN

      IF @Delimiter = ''

      BEGIN

         WITH Split AS

        ( SELECT CharOne=LEFT(@StringList,1),R=RIGHT(@StringList,len(@StringList)-1)

          UNION ALL

          SELECT LEFT(R,1), R=RIGHT(R,len(R)-1)

          FROM Split

          WHERE LEN(R)>)

        INSERT @TableList

        SELECT CharOne FROM Split

        OPTION ( MAXRECURSION 0)

        RETURN

      END -- IF

      DECLARE @XML xml

      SET @XML = '<root><csv>'+replace(@StringList,@Delimiter,'</csv><csv>')+

                 '</csv></root>'

      INSERT @TableList

      SELECT rtrim(ltrim(replace(Word.value('.','nvarchar(128)'),char(10),'')))

             AS ListMember

      FROM @XML.nodes('/root/csv') AS WordList(Word)

RETURN

END -- FUNCTION

GO

 

-- Test TVF

SELECT * FROM dbo.fnSplitStringListXML

('New York, California, Arizona, Texas, Toronto, Grand Canyon, Yosemite,

  Yellow Stone, Niagara Falls, Belgium, Denmark, Hollandia, Sweden', ',')

/* ID StringLiteral

1     New York

2     California

3     Arizona

4     Texas

....  */

 

-- Test with empty string delimiter

SELECT * FROM dbo.fnSplitStringListXML ('9876543210','')

/* ID StringLiteral

1     9

2     8

3     7

.... */

----------- 

-- Split comma-limited number list with the charindex function

 

-- Table-valued user-defined function - TVF

CREATE FUNCTION dbo.fnSplitCSV ( @NumberList varchar(4096))

RETURNS @SplitList TABLE (    ListMember INT )

AS

BEGIN

      DECLARE @Pointer int, @ListMember varchar(25)

      SET @NumberList = LTRIM(RTRIM(@NumberList))

      IF (RIGHT(@NumberList, 1) != ',')

         SET @NumberList=@NumberList+ ','

      SET @Pointer = CHARINDEX(',', @NumberList, 1)

      IF REPLACE(@NumberList, ',', '') <> ''

      BEGIN

            WHILE (@Pointer > 0)

            BEGIN

                  SET @ListMember = LTRIM(RTRIM(LEFT(@NumberList, @Pointer - 1)))

                  IF (@ListMember <> '')

                  INSERT INTO @SplitList

                        VALUES (convert(int,@ListMember))

                  SET @NumberList = RIGHT(@NumberList, LEN(@NumberList) - @Pointer)

                  SET @Pointer = CHARINDEX(',', @NumberList, 1)

            END

      END  

      RETURN

END

GO

 

-- Test

SELECT * FROM dbo.fnSplitCSV ('')

SELECT * FROM dbo.fnSplitCSV ('1000')

SELECT * FROM dbo.fnSplitCSV ('1000,4005')

SELECT * FROM dbo.fnSplitCSV ('1000,7,9')

SELECT * FROM dbo.fnSplitCSV ('1000,  3, 8494, 2329992, 8, 23, 43')

GO

 

-- XML split solution for comma-limited number list

 

-- Table-valued user-defined function - TVF

CREATE FUNCTION dbo.fnSplitCSVxml

               (@NumberList VARCHAR(4096))

RETURNS @SplitList TABLE( ListMember INT)

AS

  BEGIN

    DECLARE  @xml XML

    SET @NumberList = LTRIM(RTRIM(@NumberList))

    IF LEN(@NumberList) = 0

      RETURN

    SET @xml = '' + REPLACE(@NumberList,',','') + ''

    INSERT INTO @SplitList

    SELECT x.i.value('.','VARCHAR(MAX)') AS Member

    FROM   @xml.nodes('//n') x(i)

    RETURN

  END

GO

 

-- Test

SELECT * FROM dbo.fnSplitCSVxml ('')

SELECT * FROM dbo.fnSplitCSVxml ('1000')

SELECT * FROM dbo.fnSplitCSVxml ('1000, 1007')

SELECT * FROM dbo.fnSplitCSVxml ('1000,7,9')

SELECT * FROM dbo.fnSplitCSVxml ('1000,  3, 8494, 2329992, 8, 23, 43')

GO

 

-- Split a comma-delimited string list

 

-- Table-valued user-defined function - TVF

CREATE FUNCTION dbo.fnSplitStringList (@StringList VARCHAR(MAX))

RETURNS @TableList TABLE( StringLiteral VARCHAR(128))

BEGIN

    DECLARE @StartPointer INT, @EndPointer INT

    SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)

    WHILE (@StartPointer < LEN(@StringList) + 1)

    BEGIN

        IF @EndPointer = 0

            SET @EndPointer = LEN(@StringList) + 1

        INSERT INTO @TableList (StringLiteral)

        VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer,

                                     @EndPointer - @StartPointer))))

        SET @StartPointer = @EndPointer + 1

        SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)

    END -- WHILE

    RETURN

END -- FUNCTION

GO

 

-- Test

SELECT * FROM dbo.fnSplitStringList ('New York, California, Arizona, Texas')

GO

 

SELECT * FROM dbo.fnSplitStringList ('New York, California, Arizona, Texas,')

GO

 

SELECT * FROM dbo.fnSplitStringList ('New York')

 

SELECT * FROM dbo.fnSplitStringList ('Smith, Brown, O''Brien, Sinatra')

GO

 

------------ 

 


-- TVF and inline TVF like a view with parameter

 

-- SQL create table-valued function like a parametrized view

CREATE FUNCTION dbo.tvfGetSalesInfoByProductColor

              (@Color NVARCHAR(16))

RETURNS @retSalesInformation TABLE(-- Columns returned by the function

                                   ProductName NVARCHAR(50)    NULL,

                                   NonDiscountSales MONEY,

                                   DiscountSales    MONEY,

                                   Color            NVARCHAR(16)    NULL)

AS

  BEGIN

    INSERT @retSalesInformation

    SELECT   p.Name,

             SUM(OrderQty * UnitPrice),

             SUM((OrderQty * UnitPrice) * UnitPriceDiscount),

             @Color

    FROM     Production.Product p

             INNER JOIN Sales.SalesOrderDetail sod

               ON p.ProductID = sod.ProductID

    WHERE    Color = @Color

              OR @Color IS NULL

    GROUP BY p.Name

    

    RETURN;

  END;

 

GO

 

-- Using a TVF like a table

SELECT * FROM dbo.tvfGetSalesInfoByProductColor ('Yellow') ORDER BY ProductName

-- (34 row(s) affected)

/*

ProductName                   NonDiscountSales  DiscountSales     Color

HL Touring Frame - Yellow, 46 52404.102         0.00              Yellow

HL Touring Frame - Yellow, 50 49994.718         0.00              Yellow

HL Touring Frame - Yellow, 54 324001.9134       733.6575          Yellow

*/

 

SELECT * FROM dbo.tvfGetSalesInfoByProductColor ('') ORDER BY ProductName

-- (0 row(s) affected)

SELECT * FROM dbo.tvfGetSalesInfoByProductColor (NULL) ORDER BY ProductName

-- (266 row(s) affected)

GO

------------

 

-- SQL create INLINE table-valued function like a parametrized view

CREATE FUNCTION dbo.itvfGetSalesInfoByColor

              (@Color NVARCHAR(16))

RETURNS  TABLE  

AS

    RETURN (

    SELECT   ProductName = p.Name,

             NonDiscountSales = SUM(OrderQty * UnitPrice),

             DiscountSales = SUM((OrderQty * UnitPrice) * UnitPriceDiscount),

             Color = @Color

    FROM     Production.Product p

             INNER JOIN Sales.SalesOrderDetail sod

               ON p.ProductID = sod.ProductID

    WHERE    Color = @Color

              OR @Color IS NULL

    GROUP BY p.Name );

GO

 

-- Using an ITVF like a table

SELECT * FROM dbo.itvfGetSalesInfoByColor ('Yellow') ORDER BY ProductName

-- (34 row(s) affected)

/*

ProductName                   NonDiscountSales  DiscountSales     Color

HL Touring Frame - Yellow, 46 52404.102         0.00              Yellow

HL Touring Frame - Yellow, 50 49994.718         0.00              Yellow

HL Touring Frame - Yellow, 54 324001.9134       733.6575          Yellow

*/

 

SELECT * FROM dbo.itvfGetSalesInfoByColor ('') ORDER BY ProductName

-- (0 row(s) affected)

SELECT * FROM dbo.itvfGetSalesInfoByColor (NULL) ORDER BY ProductName

-- (266 row(s) affected)

GO

------------

 

-- Split delimited string

CREATE FUNCTION dbo.fnSplitDelimitedStringList

               (@StringList NVARCHAR(MAX),

                @Delimiter  NVARCHAR(5))

RETURNS @TableList TABLE(ID INT IDENTITY(1,1), StringLiteral NVARCHAR(1024))

  BEGIN

    DECLARE  @StartPointer INT,

             @EndPointer   INT

    SELECT @StartPointer = 1,

           @EndPointer = CHARINDEX(@Delimiter,@StringList)

    WHILE (@StartPointer < LEN(@StringList) + 1)

      BEGIN

        IF @EndPointer = 0

          SET @EndPointer = LEN(@StringList) + 1

        INSERT INTO @TableList

                   (StringLiteral)

        VALUES     (LTRIM(RTRIM(SUBSTRING(@StringList,@StartPointer,

                   @EndPointer - @StartPointer))))

        SET @StartPointer = @EndPointer + 1

        SET @EndPointer = CHARINDEX(@Delimiter,@StringList,@StartPointer)

      END -- WHILE

    RETURN

  END -- FUNCTION
GO

SELECT * FROM fnSplitDelimitedStringList ('New York; New Jersey; Texas', ';')

/* ID StringLiteral

1     New York

2     New Jersey

3     Texas

*/

------------

 

Related article:

CREATE FUNCTION (Transact-SQL)

 

The Best SQL Server Training in the World