Creating a Multi -Statement Table Valued Function in SQL Server

Question

Hi friends I want to know how to create a multi - statement table valued function in SQL Server. please explain it with example. Thank you.

Senthilkumar · Answer

Hi Vipendra, 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

Creating a Multi -Statement Table Valued Function in SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)