sqlserver 多语句表值函数例子
时间：2021-07-01 10:21:17
帮助过：4人阅读
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);
sqlserver 多语句表值函数例子
标签：mamicode lse work fun alt ada func date show
