Creating a Simple Table-Valued Function in SQL Server.

Question

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

Lajapathy Arun · Answer

Thanks Guys

MuthuMari M · Answer

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 "

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

Lajapathy Arun · Answer

CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MaxValue INT ) RETURNS @Integers TABLE ( [IntValue] INT ) AS BEGIN DECLARE @Index INT SET @Index = 1 WHILE @Index BEGIN INSERT INTO @Integers ( [IntValue] ) VALUES ( @Index ) SET @Index = @Index + 1 END RETURN END GO

Creating a Simple Table-Valued Function in SQL Server.

Answers (4)