使用sql Server 的函数功能返回 表,方便在程序中调用

 CREATE FUNCTION funCheckVatJobOrder
 (@job1 varchar(50),
  @qty1 int ,
  @job2 varchar(50),
  @qty2 int ,
  @job3 varchar(50),
  @qty3 int ,
  @job4 varchar(50),
  @qty4 int ,
  @job5 varchar(50),
  @qty5 int ,
  @job6 varchar(50),
   @qty6 int ,
  @job7 varchar(50),
  @qty7 int ,
  @job8 varchar(50),
  @qty8 int ,
  @job9 varchar(50),
  @qty9 int ,
  @job10 varchar(50),
  @qty10 int

RETURNS @emptab  table
(
fldDeptS varchar(50),
fldCompanyS varchar(50),
fldSalesOrderS varchar(50),
fldModelS varchar(50),
ScheduleQty int,
qty int,
ScheduleDateT DateTime,
fldCustCdS  varchar(50),
fldDeliveryPlaceS varchar(50),
fldJoTypeS varchar(50)
)
AS 
BEGIN
insert @emptab select fldDeptS, fldCompanyS,
      fldSalesOrderS, fldModelS,ScheduleQty,
     case when fldSalesOrderS=@job1 then @qty1 when fldSalesOrderS=@job2 then @qty2 when fldSalesOrderS=@job3 then @qty3 when fldSalesOrderS=@job4 then @qty4
   when fldSalesOrderS=@job5 then @qty5 when fldSalesOrderS=@job6 then @qty6 when fldSalesOrderS=@job7 then @qty7 when fldSalesOrderS=@job8 then @qty8
  when fldSalesOrderS=@job9 then @qty9 when fldSalesOrderS=@job10 then @qty10  else 0 end as qty, 
        ScheduleDateT,fldCustCdS,fldDeliveryPlaceS ,case when fldJoTypeS='INTERNAL' then 'NORMAL' else fldJoTypeS end as fldJoTypeS
          from JobOrder where  fldSalesOrderS=@job1  or fldSalesOrderS=@job2 or fldSalesOrderS=@job3 or fldSalesOrderS=@job4 or  fldSalesOrderS=@job5
         or fldSalesOrderS=@job6 or fldSalesOrderS=@job7 or fldSalesOrderS=@job8 or fldSalesOrderS=@job9 or  fldSalesOrderS=@job10
return


END

 

 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值