sqlserver中的表值函数和标量值函数_sql

概述顾名思义：表值函数返回的是表，而标量值函数可以返回基类型一、表值函数用户定义表值函数返回 table 数据类型。对于内联表值函数，没有函数主体；表是单个 SELECT 语句的结果集。以下示例创建了一个内联表值函数。此函数的输入参数为客户（商店）ID，而返回 ProductID、Name以及 YTD Total（销售到商店的每种产品的本年度节截止到现在的销售总额）列。 USE Advent

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

一、表值函数

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

以下示例创建了一个内联表值函数。此函数的输入参数为客户（商店）ID，而返回 ProductID、name以及 YTD Total（销售到商店的每种产品的本年度节截止到现在的销售总额）列。

USE AdventureWorks;GOCREATE FUNCTION Sales.fn_SalesByStore (@storeID int)RETURNS tableASRETURN (    SELECT P.ProductID,P.name,SUM(SD.linetotal) AS 'YTD Total'    FROM Production.Product AS P       JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID      JOIN Sales.SalesOrderheader AS SH ON SH.SalesOrderID = SD.SalesOrderID    WHERE SH.CustomerID = @storeID    GROUP BY P.ProductID,P.name);GO

以下示例调用此函数并指定客户 ID 为 602

SELECT * FROM Sales.fn_SalesByStore (602);

对于多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-sql 语句，这些语句可生成行并将其插入将返回的表中。

以下示例创建了一个表值函数。此函数具有一个输入参数 EmployeeID 而返回直接或间接向指定员工报告的所有员工的列表。

FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)@retFindReports table ( EmployeeID int primary key NOT NulL,name nvarchar(255) 50) int nvarchar (@H_403_179@NulL)--Returns a result set that Lists all the employees who report to the --specific employee directly or indirectly.*/BEGIN WITH DirectReports(name,Title,EmployeeID,EmployeeLevel,Sort) AS (SELECT CONVERT(Varchar(255),c.Firstname + ' ' + c.Lastname),e.Title,e.EmployeeID,1,+ c.Lastname) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID @InEmpID UNION ALL REPliCATE (| ',EmployeeLevel) + c.Firstname + @H_403_179@CONVERT (RTRIM(Sort) + Firstname + Lastname) as e = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID,name,Sort FROM DirectReports RETURNEND;在以下示例中，调用了此函数。

-- Example invocationSELECT EmployeeID,EmployeeLevelFROM dbo.fn_FindReports(109)ORDER BY Sort;二、标量值函数

写一个标量值函数 FUNCTION [dbo].testGetSubNodes_]( @nodeID int)intBEGIN declare @nodeCount int select @nodeCount=5 from MenuTree return @nodeCountEND

这个函数很简单返回一个整型值，然后就可以在存储过程中调用了，不过调用的方式有所不同，象上面的表值函数调用是不需要所有者的，只要写函数名称就可以，对于标量值函数来说，是需要加上所有者的，比如所有者是dbo select dbo.testGetSubNodes_,这样就可以返回5，如果不加dbo，那sql会不认识这个函数。再来一个标量值函数：

FUNCTION FUN_DataFormat (@strDate datetime)  varchar(20)  AS  BEGIN         @date 20)      set @date = DATEname(YY,@strDate)+年'@H_403_179@Convert(VARCHAR,255); line-height:1.5!important">MONTH(@strDate))月DAY(日@dateEND