SYbase存储过程写法
各位达人,由于不熟悉数据库东东,下面是我东拉西扯一个存储过程.请帮忙改成可执行,谢谢!
create procedure P_BranchTdTotalSMnumAverageCount @tradeKey varchar(200),@startDate char(8),@endDate char(8),@deptID varchar(30),@termKey varchar(20)
as
BEGIN
select deptDesc,deptName,sum(num) as totaltd,count(1) as totalsm,totaltd/totalsm as average from (
select d.deptDesc,d.deptName,count(1) as num,a.smNo,count(a) from TradeLog a,SelfMachineInfo b,Dept c,Dept d
where a.smNo=b.smNo and b.deptID=c.deptID and d.deptID=c.prarentDeptID and a.tradeCode!='3954'
if(@tradeKey!='')
and (a.tradeCode like '%@tradeKey%' or a.tradeDesc like '%@tradeKey%')
if(@startDate!='')
and a.tradeDate >= '@startDate'
if(@endDate!='')
and a.tradeDate <= '@endDate'
if(@deptID!='')
begin
and (c.deptDesc='@deptID'
declare deptList cursor for select b.deptDesc from Dept a,Dept b where a.deptDesc = '@deptID' and b.prarentDeptID = a.deptID
open deptList
declare @subDeptID varchar(30)
if(游标的列数 > 0)
begin
or c.deptDesc in ( '
fetch deptList into @subDeptID
while @@sqlstatus != 2
begin
if @@sqlstatus =1
begin
raiserror 20001 "select fail"
return
end
if(不是游标最后一列)
@subDeptID ','
else
@subDeptID
end
')
end
close deptList
deallocate cursor deptList
)
end
if(@termKey!='')
and a.smNo like '%@termKey%'
group by d.deptDesc,d.deptName,c.prarentDeptID,a.smNo
) group by deptDesc order by average
END