Table-valued functions and scalar-valued functions in SQL Server

Source: Internet
Author: User
Tags scalar

As the name implies: a table-valued function returns a table, and a scalar-valued function can return a base type

First, table-valued functions

The user-defined table-valued function returns the table data type. For inline table-valued functions, there is no function body; The table is the result set of a single SELECT statement.

The following example creates an inline table-valued function. The input parameters for this function are the customer (store) ID, and the return ProductID , Name and YTD Total (the year-to-date sales total for each product sold to the store) column.

 UseAdventureWorks;GOCREATE FUNCTIONSales.fn_salesbystore (@storeid int)RETURNS TABLE asRETURN (    SELECTP.productid,,SUM(SD. LineTotal) as 'YTD Total'     fromProduction.Product asPJOINSales.SalesOrderDetail asSd onTdi ProductID=P.productidJOINSales.SalesOrderHeader asSh onSh. SalesOrderID=SD. SalesOrderIDWHERESh. CustomerID= @storeid    GROUP  byP.productid,;GO
The following example calls this function and specifies that the customer ID is 602
SELECT * from Sales.fn_salesbystore (602);

For multi-statement table-valued functions, at BEGIN ... The body of the function defined in the END statement block contains a series of Transact-SQL statements that generate rows and insert them into the table that will be returned.

The following example creates a table-valued function. This function has an input parameter EmployeeID and returns a list of all employees that are reported directly or indirectly to the specified employee.

 UseAdventureWorks;GOCREATE FUNCTIONDbo.fn_findreports (@InEmpID INTEGER)RETURNS @retFindReports TABLE(EmployeeIDint Primary Key  not NULL, Namenvarchar(255) not NULL, Titlenvarchar( -) not NULL, Employeelevelint  not NULL, Sortnvarchar(255) not NULL)--Returns a result set that lists all the employees--specific employee directly or indirectly.*/ asBEGIN    withDirectreports (Name, Title, EmployeeID, Employeelevel, Sort) as    (SELECT CONVERT(Varchar(255), C.firstname+ ' ' +c.lastname), E.title, E.employeeid,1,        CONVERT(Varchar(255), C.firstname+ ' ' +c.lastname) fromHumanResources.Employee aseJOINPerson.Contact asC onE.contactid=C.contactidWHEREE.employeeid= @InEmpID   UNION  All     SELECT CONVERT(Varchar(255),REPLICATE('| ', Employeelevel)+C.firstname+ ' ' +c.lastname), E.title, E.employeeid, Employeelevel+ 1,        CONVERT(Varchar(255),RTRIM(Sort)+ '| ' +FirstName+ ' ' +LastName) fromHumanResources.Employee aseJOINPerson.Contact asC onE.contactid=C.contactidJOINDirectreports asD onE.managerid=D.employeeid)--Copy the required columns to the result of the function   INSERT @retFindReports   SELECTEmployeeID, Name, Title, Employeelevel, Sort fromDirectreportsRETURNEND;GO
In the following example, this function is called.
--Example Invocationselect EmployeeID, Name, Title, Employeelevelfrom dbo.fn_findreports (109) ORDER by Sort;

Second, scalar value function
Write a scalar value function
CREATE FUNCTION [dbo].[Testgetsubnodes_]( @nodeId int)RETURNS int asBEGIN    Declare @nodeCount int Select @nodeCount=5  fromMenutreereturn @nodeCountEND
This function simply returns an integer value, which can then be called in a stored procedure, but is called differently, as the table-valued function call above does not require an owner, as long as the function name is written, and for a scalar-valued function, it needs to be added to the owner, such as the owner is Dboselect Dbo.testgetsubnodes_, so you can return 5,If you do not add the dbo, then SQL will not recognize this function. one more scalar-valued function:
CREATE FUNCTIONFun_dataformat (@strDate datetime)  RETURNS varchar( -) as  BEGIN         Declare @date varchar( -)      Set @date = Datename(YY,@strDate)+'years'+Convert(VARCHAR,MONTH(@strDate))+'Month'+Convert(VARCHAR, Day(@strDate))+'Day'    return @dateEND

You can use SELECT dbo. Fun_dataformat (GETDATE ()) to use.

Table-valued functions and scalar-valued functions in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.