Creating a Simple Table-Valued Function in SQL Server.

Vipendra Verma

14y4.3k

Hi friends

I want to know how to create a simple table-valued function in SQL Server.

Thank you.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Lajapathy Arun14y ago
Thanks Guys
0
MuthuMari M14y ago
Hi,

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.
The following uses the Customer table in the Northwind database to show how an inline table-valued function is implemented.
```
USE Northwind go CREATE FUNCTION fx_Customers_ByCity ( @City nvarchar(15) ) RETURNS table AS RETURN (         SELECT CompanyName         FROM Customers         WHERE City =@City  ) go SELECT * FROM fx_Customers_ByCity('London')  CompanyName ---------------------------------------- Around the Horn . . .  Seven Seas Imports

pls refer this url for more details
http://www.sqlteam.com/article/intro-to-user-defined-functions-updated

thanks 
```
If this post is useful then mark it as "Accepted Answer"
0
Senthilkumar14y ago
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
0
Lajapathy Arun14y ago
CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MaxValue INT )

RETURNS @Integers TABLE ( [IntValue] INT )

AS

BEGIN

    DECLARE @Index    INT

    SET @Index = 1

    WHILE @Index <= @MaxValue

    BEGIN

        INSERT INTO @Integers ( [IntValue] ) VALUES ( @Index )

        SET @Index = @Index + 1

    END

    RETURN

END

GO
0