自定义函数中返回表的例子

USE [作业数据库]
GO

SET QUOTED_IDENTIFIER ON
GO
 ALTER function [dbo].[FK_baogaof1](@time datetime)  --  drop function FK_baogaof1
 returns @mytable TABLE(委托报告量 varchar(100),CECR1 int,CECR11 int,CECR3 int,CECR16 int,CECR6 int,CECR7 int,OTHERS int,总量 int)
 as
 begin

 declare @CECR1Num int
 declare @CECR11Num int
 declare @CECR3Num int
 declare @CECR16 int
 declare @CECR6 int
 declare @CECR7 int
 declare @OtherNum int
 declare @total int
 declare @time1 datetime
 set @time1=null
 
 
 --------------如果是星期一，查询：逾期量 今天 星期二三四五 和下星期一。
 if datename(weekday, @time)= '星期一'
 begin
 -----------------------逾期量--------------------------------------------------------------------------------------------
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
  insert  @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

  --今天
  select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 1, @time) --星期二
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 2, @time) --星期三
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 3, @time) --星期四
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 4, @time) --星期五
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total
 
  set @time1=dateadd(day, 7, @time) --下周一
  select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
  select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
  select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
  select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
  select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
  select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
  select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
  set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
 insert  @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total