DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create and apply table-valued functions?

The following Microsoft SQL Server T-SQL scripts demonstrate how to create and invoke table-valued functions.

CONCAT and FORMAT functions are new in SQL Server 2012.

-- T-SQL Table-Valued Function QUICK SYNTAX - inline table-valued function

USE AdventureWorks2008;

GO

CREATE FUNCTION tvfnPurchasesByVendor (@VendorID INT)

RETURNS TABLE

AS

  RETURN

    (SELECT   P.ProductID,

              P.Name,

              SUM(POD.LineTotal) AS [YTD Total]

     FROM     Production.Product AS P

              INNER JOIN Purchasing.PurchaseOrderDetail AS POD

                ON POD.ProductID = P.ProductID

              INNER JOIN Purchasing.PurchaseOrderHeader AS POH

                ON POH.PurchaseOrderID = POD.PurchaseOrderID

     WHERE    POH.VendorID = @VendorID

     GROUP BY P.ProductID, P.Name);

GO

-- SQL Server apply / execute / call table-valued function

SELECT * FROM  tvfnPurchasesByVendor (1538)

/*    ProductID   Name              YTD Total

      928         LL Mountain Tire  886751.25

      929         ML Mountain Tire  1005427.50  */

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

-- CROSS APPLY usage with system table-valued function - DMF

SELECT TOP 10

          DBName        = DB_NAME(eqt.dbid)

         ,TotalReads    = SUM(total_logical_reads)

         ,ExecuteCount  = SUM(eqs.execution_count)

FROM sys.dm_exec_query_stats eqs

  CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) as eqt

GROUP BY DB_NAME(eqt.dbid)

ORDER BY ExecuteCount DESC;

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

-- SQL Server create table-valued function - user-defined function

USE AdventureWorks;

GO

CREATE FUNCTION dbo.tvfnListStaffOfSupervisor  (@SuperVisorID INT)

RETURNS @StaffListing TABLE(EmployeeID   INT,

                            FullName     NVARCHAR(128),

                            Title        NVARCHAR(64),

                            OrgLevel     INT,

                            OrgChartSort NVARCHAR(128))

AS

  BEGIN

    -- Recursive CTE - Common Table Expression

    WITH ImmediateSupervisor(Name,Title,EmployeeID,OrgLevel,Sorting)

         AS (SELECT CAST(CONCAT(c.FirstName, ' ', c.LastName) AS NVARCHAR(128)),

                    e.Title,

                    e.EmployeeID,

                    0,

                    CAST(c.FirstName + ' ' + c.LastName  AS NVARCHAR(128))

             FROM   HumanResources.Employee AS e

                    INNER JOIN Person.Contact AS c

                      ON e.ContactID = c.ContactID

             WHERE  e.EmployeeID = @SuperVisorID

             UNION ALL

  SELECT  CAST(REPLICATE('> ',OrgLevel+1)+c.FirstName+' '+c.LastName AS NVARCHAR(128)),

                    e.Title,

                    e.EmployeeID,

                    OrgLevel + 1,

  CAST(RTRIM(Sorting) + '> ' + FirstName + ' ' + LastName  AS NVARCHAR(128))

             FROM   HumanResources.Employee AS e

                    INNER JOIN Person.Contact AS c

                      ON e.ContactID = c.ContactID

                    INNER JOIN ImmediateSupervisor AS s

                      ON e.ManagerID = s.EmployeeID)

    INSERT @StaffListing

    SELECT EmployeeID,

           Name,

           Title,

           OrgLevel,

           Sorting

    FROM   ImmediateSupervisor

    RETURN

  END;

GO

 

-- Example for table-valued function call / execution / invocation

SELECT OrgLevel,

       FullName,

       EmployeeID,

       Title

FROM     dbo.tvfnListStaffOfSupervisor(12)

ORDER BY OrgChartSort;

/*

OrgLevel    FullName          EmployeeID  Title

0           Terri Duffy             12    Vice President of Engineering

1           > Roberto Tamburello    3     Engineering Manager

2           > > Dylan Miller        158   Research and Development Manager

3           > > > Diane Margheim    79    Research and Development Engineer

3           > > > Gigi Matthew      114   Research and Development Engineer

3           > > > Michael Raheem    217   Research and Development Manager

2           > > Gail Erickson       9     Design Engineer

2           > > Jossef Goldberg     11    Design Engineer

2           > > Michael Sullivan    267   Senior Design Engineer

2           > > Ovidiu Cracium      263   Senior Tool Designer

3           > > > Janice Galvin     265   Tool Designer

3           > > > Thierry D'Hers    5     Tool Designer

2           > > Rob Walters         4     Senior Tool Designer

2           > > Sharon Salavaria    270   Design Engineer  */

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

 

-- T-SQL table-valued function - user-defined function - udf

USE pubs

GO

CREATE FUNCTION fnSalesByAuthorForState

               (@State CHAR(2))

RETURNS TABLE

AS

  RETURN

    (SELECT   TOP ( 2 ^ 32 ) [State] = @State,

                             SSN = a.au_id,

                             FirstName = a.au_fname,

                             LastName = a.au_lname,

                             QtySold = SUM(s.qty),

                             Revenue = FORMAT(sum(t.price * s.qty),'c','en-US')

     FROM     Authors a

              INNER JOIN TitleAuthor ta

                ON a.au_id = ta.au_id

              INNER JOIN Titles t

                ON t.title_id = ta.title_id

              INNER JOIN Sales s

                ON s.title_id = t.title_id

              INNER JOIN Stores st

                ON st.Stor_ID = s.stor_id

     WHERE    st.state = @State

     GROUP BY a.au_id,

              a.au_fname,

              a.au_lname

     ORDER BY QtySold DESC,

              LastName,

              FirstName)

 

GO

 

SELECT *

FROM   fnSalesByAuthorForState('CA')

UNION

SELECT *

FROM   fnSalesByAuthorForState('WA')

GO

/* Partial results

 

State SSN         FirstName   LastName    QtySold     Revenue

CA    213-46-8915 Marjorie    Green       35          $104.65

CA    267-41-2394 Michael     O'Leary     20          $299.80

CA    274-80-9391 Dean        Straight    15          $299.85

CA    427-17-2319 Ann         Dull        50          $1,000.00

*/

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

Related link:

Table-Valued User-Defined Functions

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE