Creating a Multi -Statement Table Valued Function in SQL Server

The multi-statement table-valued function is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. Unlike the inline table-valued function, a table variable must be explicitly declared and defined. The following example shows how to implement a multi-statement table-valued function that populates and returns a table variable.

Table valued function is bit different from commonly used scalar valued function, where it returns a single value. But table valued function returns table (in form of columns and rows). Usage of table valued function is bit different from that of scalar valued function. Let create a table valued function and check its usage.

During development we commonly need our comma separated string values in a table format to use in different type of queries.

USE Northwind

go

CREATE FUNCTION fx_OrdersByDateRangeAndCount

( @OrderDateStart smalldatetime, 

  @OrderDateEnd smalldatetime, 

  @OrderCount smallint )

RETURNS @OrdersByDateRange TABLE

  (  CustomerID nchar(5),

     CompanyName nvarchar(40),

     OrderCount smallint,

     Ranking char(1) )

AS

BEGIN

 --Statement 1

 INSERT @OrdersByDateRange

 SELECT a.CustomerID,

        a.CompanyName,

        COUNT(a.CustomerID)AS OrderCount,

       'B'

 FROM Customers a

 JOIN Orders b ON a.CustomerID =b.CustomerID

 WHERE OrderDate BETWEEN @OrderDateStart AND @OrderDateEnd

 GROUP BY a.CustomerID,a.CompanyName

 HAVING COUNT(a.CustomerID)>@OrderCount

--Statement 2

UPDATE @OrdersByDateRange

SET Ranking ='A'

WHERE CustomerID IN (SELECT TOP 5 WITH TIES CustomerID

                     FROM (SELECT a.CustomerID,

                                  COUNT(a.CustomerID)AS OrderTotal

                           FROM Customers a

                           JOIN Orders b ON a.CustomerID =b.CustomerID

                           GROUP BY a.CustomerID) AS DerivedTable

                           ORDER BY OrderTotal DESC)

RETURN

END

For more info:
http://www.sqlteam.com/article/intro-to-user-defined-functions-updated
http://connectsql.blogspot.in/2010/12/sql-server-table-valued-function.html