多语句表值函数顾名思义是返回了一张表,可以传入多个参数
1.定义
1 Use AdventureWorks2014; 2 go 3 if exists(select * from sys.objects where name=‘udf_SEL_SalesQuota‘) 4 drop function dbo.udf_SEL_SalesQuota; 5 go 6 CREATE FUNCTION dbo.udf_SEL_SalesQuota ( @BusinessEntityID int, @ShowHistory bit ) 7 RETURNS @SalesQuota TABLE 8 ( 9 BusinessEntityID int, 10 QuotaDate datetime, 11 SalesQuota money 12 ) 13 as 14 begin 15 INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota) 16 SELECT BusinessEntityID, ModifiedDate, SalesQuota 17 FROM Sales.SalesPerson 18 WHERE BusinessEntityID = @BusinessEntityID; 19 20 IF @ShowHistory = 1 21 begin 22 INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota) 23 SELECT BusinessEntityID, QuotaDate, SalesQuota 24 FROM Sales.SalesPersonQuotaHistory 25 WHERE BusinessEntityID = @BusinessEntityID; 26 end 27 28 return 29 30 end
2.调用
1 Use AdventureWorks2014; 2 GO 3 SELECT BusinessEntityID, QuotaDate, SalesQuota 4 FROM dbo.udf_SEL_SalesQuota (275,0);