sqlserver 多语句表值函数例子

多语句表值函数顾名思义是返回了一张表,可以传入多个参数

1.定义

Use AdventureWorks2014;
go
if exists(select * from sys.objects where name='udf_SEL_SalesQuota')
drop function dbo.udf_SEL_SalesQuota;
go
CREATE FUNCTION dbo.udf_SEL_SalesQuota ( @BusinessEntityID int, @ShowHistory bit )
RETURNS @SalesQuota TABLE 
    (
        BusinessEntityID int, 
        QuotaDate datetime, 
        SalesQuota money
    )
as
begin
    INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota)
    SELECT BusinessEntityID, ModifiedDate, SalesQuota
    FROM Sales.SalesPerson
    WHERE BusinessEntityID = @BusinessEntityID;

    IF @ShowHistory = 1
         begin
            INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota)
            SELECT BusinessEntityID, QuotaDate, SalesQuota
            FROM Sales.SalesPersonQuotaHistory
            WHERE BusinessEntityID = @BusinessEntityID;
         end

    return

end

2.调用

Use AdventureWorks2014;
GO
SELECT BusinessEntityID, QuotaDate, SalesQuota      
FROM dbo.udf_SEL_SalesQuota (275,0);