兄弟们帮忙看下,本人第一次写存储过程!
ALTER PROCEDURE [dbo].[sp_AllCountNumber]
(
@deptId nvarchar(50),@userId nvarchar(50),@sWhere nvarchar(2000),@countAllOrder int output,@countPass int output,
@countBell int output,@countTemporaryBell int output,@countNoWork int output, @countTemporary int output,
@countDue int output,@countPay int output,@countNoPay int outPut,@countChk int outPut,@countNoChk int OutPut
)
as
declare @sql nvarchar(2000)
declare @sqlPass nvarchar(2000)
declare @sqlBell nvarchar(2000)
declare @sqlTemporary nvarchar(2000)
declare @sqlNoWork nvarchar(2000)
declare @sqlTemp nvarchar(2000)
declare @sqlDue nvarchar(2000)
declare @sqlPay nvarchar(2000)
declare @sqlNoPay nvarchar(2000)
declare @sqlChk nvarchar(2000)
declare @sqlNoChk nvarchar(2000)
begin
set @sql='select @countAllOrder = count(*) from My_RechargeOrder'
set @sqlPass='select @countPass= count(*) from My_RechargeOrder where PassState=1 '
set @sqlBell ='select @countBell= count(*) from My_RechargeOrder where PassState=3'
set @sqlTemporary ='select @countTemporaryBell = count(*) from My_RechargeOrder where PassState=4'
set @sqlNoWork ='select @countNoWork = count(*) from My_RechargeOrder where PassState=0'
set @sqlTemp ='select @CountTemporary = count(*) from My_RechargeOrder where isStuff=1'
set @sqlDue ='select @countDue = count(*) from My_RechargeOrder where isStuff=0'
set @sqlPay ='select @countPay = count(*) from My_RechargeOrder where PayState=1'
set @sqlNoPay ='select @countNoPay = count(*) from My_RechargeOrder where PayState=0'
set @sqlChk ='select @countChk = count(*) from My_RechargeOrder where ChkState=1'
set @sqlNoChk ='select @countNoChk = count(*) from My_RechargeOrder where ChkState=0'
if(@sWhere is not null)
begin
set @sql='select @countAllOrder = count(*) from My_RechargeOrder @sWhere'
set @sqlPass='select @countPass= count(*) from My_RechargeOrder where PassState=1 @sWhere'
set @sqlBell ='select @countBell= count(*) from My_RechargeOrder where PassState=3 @sWhere'
set @sqlTemporary ='select @countTemporaryBell = count(*) from My_RechargeOrder where PassState=4 @sWhere'
set @sqlNoWork ='select @countNoWork = count(*) from My_RechargeOrder where PassState=0 @sWhere'
set @sqlTemp ='select @CountTemporary = count(*) from My_RechargeOrder where isStuff=1 @sWhere'
set @sqlDue ='select @countDue = count(*) from My_RechargeOrder where isStuff=0 @sWhere'
set @sqlPay ='select @countPay = count(*) from My_RechargeOrder where PayState=1 @sWhere'
set @sqlNoPay ='select @countNoPay = count(*) from My_RechargeOrder where PayState=0 @sWhere'
set @sqlChk ='select @countChk = count(*) from My_RechargeOrder where ChkState=1 @sWhere'
set @sqlNoChk ='select @countNoChk = count(*) from My_RechargeOrder where ChkState=0 @sWhere'
set @sql=replace(@sql,'@sWhere',@sWhere)
set @sqlPass=replace(@sql,'@sWhere',@sWhere)
set @sqlBell=replace(@sql,'@sWhere',@sWhere)
set @sqlTemporary=replace(@sql,'@sWhere',@sWhere)
set @sqlNoWork=replace(@sql,'@sWhere',@sWhere)
set @sqlTemp=replace(@sql,'@sWhere',@sWhere)
set @sqlDue=replace(@sql,'@sWhere',@sWhere)
set @sqlPay=replace(@sql,'@sWhere',@sWhere)
set @sqlNoPay=replace(@sql,'@sWhere',@sWhere)
set @sqlChk=replace(@sql,'@sWhere',@sWhere)
set @sqlNoChk=replace(@sql,'@sWhere',@sWhere)
end
exec(@sql)
exec(@sqlPass)
exec(@sqlBell)
exec(@sqlTemporary)
exec(@sqlNoWork)
exec(@sqlTemp)
exec(@sqlDue)
exec(@sqlPay)
exec(@sqlNoPay)
exec(@sqlChk)
exec(@sqlNoChk)
enddeclare @countAllOrder int
declare @countPass int
declare @countBell int
declare @countTemporaryBell int
declare @countNoWork int
declare @countTemporary int
declare @countDue int
declare @countPay int
declare @countNoPay int
declare @countChk int
declare @countNoChk int
exec sp_AllCountNumber '','','',@countAllOrder output,@countPass output,
@countBell output,@countTemporaryBell output,@countNoWork output, @countTemporary output,
@countDue output,@countPay output,@countNoPay outPut,@countChk outPut,@countNoChk OutPut
ALTER PROCEDURE [dbo].[sp_AllCountNumber]
(
@deptId nvarchar(50),@userId nvarchar(50),@sWhere nvarchar(2000),@countAllOrder int output,@countPass int output,
@countBell int output,@countTemporaryBell int output,@countNoWork int output, @countTemporary int output,
@countDue int output,@countPay int output,@countNoPay int outPut,@countChk int outPut,@countNoChk int OutPut
)
as
declare @sql nvarchar(2000)
declare @sqlPass nvarchar(2000)
declare @sqlBell nvarchar(2000)
declare @sqlTemporary nvarchar(2000)
declare @sqlNoWork nvarchar(2000)
declare @sqlTemp nvarchar(2000)
declare @sqlDue nvarchar(2000)
declare @sqlPay nvarchar(2000)
declare @sqlNoPay nvarchar(2000)
declare @sqlChk nvarchar(2000)
declare @sqlNoChk nvarchar(2000)
begin
set @sql='select @countAllOrder = count(*) from My_RechargeOrder'
set @sqlPass='select @countPass= count(*) from My_RechargeOrder where PassState=1 '
set @sqlBell ='select @countBell= count(*) from My_RechargeOrder where PassState=3'
set @sqlTemporary ='select @countTemporaryBell = count(*) from My_RechargeOrder where PassState=4'
set @sqlNoWork ='select @countNoWork = count(*) from My_RechargeOrder where PassState=0'
set @sqlTemp ='select @CountTemporary = count(*) from My_RechargeOrder where isStuff=1'
set @sqlDue ='select @countDue = count(*) from My_RechargeOrder where isStuff=0'
set @sqlPay ='select @countPay = count(*) from My_RechargeOrder where PayState=1'
set @sqlNoPay ='select @countNoPay = count(*) from My_RechargeOrder where PayState=0'
set @sqlChk ='select @countChk = count(*) from My_RechargeOrder where ChkState=1'
set @sqlNoChk ='select @countNoChk = count(*) from My_RechargeOrder where ChkState=0'
if(@sWhere is not null)
begin
set @sql='select @countAllOrder = count(*) from My_RechargeOrder @sWhere'
set @sqlPass='select @countPass= count(*) from My_RechargeOrder where PassState=1 @sWhere'
set @sqlBell ='select @countBell= count(*) from My_RechargeOrder where PassState=3 @sWhere'
set @sqlTemporary ='select @countTemporaryBell = count(*) from My_RechargeOrder where PassState=4 @sWhere'
set @sqlNoWork ='select @countNoWork = count(*) from My_RechargeOrder where PassState=0 @sWhere'
set @sqlTemp ='select @CountTemporary = count(*) from My_RechargeOrder where isStuff=1 @sWhere'
set @sqlDue ='select @countDue = count(*) from My_RechargeOrder where isStuff=0 @sWhere'
set @sqlPay ='select @countPay = count(*) from My_RechargeOrder where PayState=1 @sWhere'
set @sqlNoPay ='select @countNoPay = count(*) from My_RechargeOrder where PayState=0 @sWhere'
set @sqlChk ='select @countChk = count(*) from My_RechargeOrder where ChkState=1 @sWhere'
set @sqlNoChk ='select @countNoChk = count(*) from My_RechargeOrder where ChkState=0 @sWhere'
set @sql=replace(@sql,'@sWhere',@sWhere)
set @sqlPass=replace(@sql,'@sWhere',@sWhere)
set @sqlBell=replace(@sql,'@sWhere',@sWhere)
set @sqlTemporary=replace(@sql,'@sWhere',@sWhere)
set @sqlNoWork=replace(@sql,'@sWhere',@sWhere)
set @sqlTemp=replace(@sql,'@sWhere',@sWhere)
set @sqlDue=replace(@sql,'@sWhere',@sWhere)
set @sqlPay=replace(@sql,'@sWhere',@sWhere)
set @sqlNoPay=replace(@sql,'@sWhere',@sWhere)
set @sqlChk=replace(@sql,'@sWhere',@sWhere)
set @sqlNoChk=replace(@sql,'@sWhere',@sWhere)
end
exec(@sql)
exec(@sqlPass)
exec(@sqlBell)
exec(@sqlTemporary)
exec(@sqlNoWork)
exec(@sqlTemp)
exec(@sqlDue)
exec(@sqlPay)
exec(@sqlNoPay)
exec(@sqlChk)
exec(@sqlNoChk)
enddeclare @countAllOrder int
declare @countPass int
declare @countBell int
declare @countTemporaryBell int
declare @countNoWork int
declare @countTemporary int
declare @countDue int
declare @countPay int
declare @countNoPay int
declare @countChk int
declare @countNoChk int
exec sp_AllCountNumber '','','',@countAllOrder output,@countPass output,
@countBell output,@countTemporaryBell output,@countNoWork output, @countTemporary output,
@countDue output,@countPay output,@countNoPay outPut,@countChk outPut,@countNoChk OutPut
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@countAllOrder"。这是个什么情况?
改成
set @sql='select ' + convert(varchar(20),@ countAllOrder ) + ' = count(*) from My_RechargeOrder'剩下的楼主自己以此类推的改
这个存储过程写的真蛋疼
set @sqlPass='select @countPass= count(*) from My_RechargeOrder where PassState=1 '
set @sqlBell ='select @countBell= count(*) from My_RechargeOrder where PassState=3'
set @sqlTemporary ='select @countTemporaryBell = count(*) from My_RechargeOrder where PassState=4'
set @sqlNoWork ='select @countNoWork = count(*) from My_RechargeOrder where PassState=0'
set @sqlTemp ='select @CountTemporary = count(*) from My_RechargeOrder where isStuff=1'
set @sqlDue ='select @countDue = count(*) from My_RechargeOrder where isStuff=0'
set @sqlPay ='select @countPay = count(*) from My_RechargeOrder where PayState=1'
set @sqlNoPay ='select @countNoPay = count(*) from My_RechargeOrder where PayState=0'
set @sqlChk ='select @countChk = count(*) from My_RechargeOrder where ChkState=1'
set @sqlNoChk ='select @countNoChk = count(*) from My_RechargeOrder where ChkState=0'这个可以换成下面一个语句 select
AllOrder = count(*),
Pass=sum(case when PassState=1 then 1 else 0 end),
Bell=sum(case when PassState=3 then 1 else 0 end),
TemporaryBell=sum(case when PassState=4 then 1 else 0 end),
NoWork=sum(case when PassState=0 then 1 else 0 end),
Temporary=sum(case when isStuff=1 then 1 else 0 end),
Due=sum(case when isStuff=0 then 1 else 0 end),
Pay=sum(case when PayState=1 then 1 else 0 end),
NoPay=sum(case when PayState=0 then 1 else 0 end),
Chk=sum(case when ChkState=1 then 1 else 0 end),
NoChk=sum(case when ChkState=0 then 1 else 0 end)
from My_RechargeOrder
下面的类似。自己改吧。
@deptId nvarchar(50),
@userId nvarchar(50),
@sWhere nvarchar(2000)
as
declare @str varchar(8000)
set @str='
select
AllOrder = count(*),
Pass=sum(case when PassState=1 then 1 else 0 end),
Bell=sum(case when PassState=3 then 1 else 0 end),
TemporaryBell=sum(case when PassState=4 then 1 else 0 end),
NoWork=sum(case when PassState=0 then 1 else 0 end),
Temporary=sum(case when isStuff=1 then 1 else 0 end),
Due=sum(case when isStuff=0 then 1 else 0 end),
Pay=sum(case when PayState=1 then 1 else 0 end),
NoPay=sum(case when PayState=0 then 1 else 0 end),
Chk=sum(case when ChkState=1 then 1 else 0 end),
NoChk=sum(case when ChkState=0 then 1 else 0 end)
from My_RechargeOrder 'if(@sWhere is not null)
set @str=@str+@sWhereexec(@str)go