lastname

表值函数，标量值函数详解

顾名思义：表值函数返回的是表，⽽标量值函数可以返回基类型

1.表值函数

⽤户定义表值函数返回table数据类型。对于内联表值函数，没有函数主体；表是单个SELECT语句的结果集。

以下⽰例创建了⼀个内联表值函数。此函数的输⼊参数为客户（商店）ID，⽽返回ProductID、Name以及YTDTotal（销售到商店的每种

产品的本年度节截⽌到现在的销售总额）列。

USEAdventureWorks;

GO

_SalesByStore(@storeidint)

RETURNSTABLE

AS

RETURN

(

tID,,SUM(tal)AS'YTDTotal'

tASP

tID=tID

rderID=rderID

erID=@storeid

tID,

);

GO

以下⽰例调⽤此函数并指定客户ID为602

SELECT*_SalesByStore(602);

对于多语句表值函数，在BEGIN...END语句块中定义的函数体包含⼀系列Transact-SQL语句，这些语句可⽣成⾏并将其插⼊将返回的

表中。

以下⽰例创建了⼀个表值函数。此函数具有⼀个输⼊参数

EmployeeID

⽽返回直接或间接向指定员⼯报告的所有员⼯的列表。

USEAdventureWorks;

GO

_FindReports(@InEmpIDINTEGER)

RETURNS@retFindReportsTABLE

(

EmployeeIDintprimarykeyNOTNULL,

Namenvarchar(255)NOTNULL,

Titlenvarchar(50)NOTNULL,

EmployeeLevelintNOTNULL,

Sortnvarchar(255)NOTNULL

)

--Returnsaresulttthatlistsalltheemployeeswhoreporttothe

--specificemployeedirectlyorindirectly.*/

--specificemployeedirectlyorindirectly.*/

AS

BEGIN

WITHDirectReports(Name,Title,EmployeeID,EmployeeLevel,Sort)AS

(SELECTCONVERT(Varchar(255),ame+''+me),

,

eeID,

1,

CONVERT(Varchar(255),ame+''+me)

eeASe

tID=tID

eeID=@InEmpID

UNIONALL

SELECTCONVERT(Varchar(255),REPLICATE('|',EmployeeLevel)+

ame+''+me),

,

eeID,

EmployeeLevel+1,

CONVERT(Varchar(255),RTRIM(Sort)+'|'+FirstName+''+

LastName)

eea

tID=tID

rID=eeID

)

--copytherequiredcolumnstotheresultofthefunction

INSERT@retFindReports

SELECTEmployeeID,Name,Title,EmployeeLevel,Sort

FROMDirectReports

RETURN

END;

GO

在以下⽰例中，调⽤了此函数。

--Exampleinvocation

SELECTEmployeeID,Name,Title,EmployeeLevel

_FindReports(109)

ORDERBYSort;

2.标量值函数

写⼀个标量值函数

ALTERFUNCTION[

dbo].[testGetSubNodes_]

(

@nodeIdint

)

RETURNSint

AS

BEGIN

declare@nodeCountint

lect@nodeCount=5fromMenuTree

return@nodeCount

END

这个函数很简单返回⼀个整型值，然后就可以在存储过程中调⽤了，不过调⽤的⽅式有所不同，象上⾯的表值函数调⽤是不需要所有者的，只要写函数

名称就可以，对于标量值函数来说，是需要加上所有者的，⽐如所有者是dbo

tSubNodes_,这样就可以返回5，

如果不加dbo，那sql会不认识这个函数。