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

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, P.name,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, p.name);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