大家看看这个存储过程怎么错了?
ALTER PROCEDURE db_owner.FinanceStat
@year int
AS
declare @i int
declare @raccount float
declare @caccount float
declare @account float
declare @sql varchar(500)
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
set @raccount=SELECT ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)=@year and month(addtime)=@i
set @caccount=SELECT ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)=@year and month(addtime)=@i
set @account=SELECT ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0) AS account where status=0 and year(addtime)=@year and month(addtime)=@i
insert into #oo(mon,raccount,caccount,account) values(@i,@raccount,@caccount,@account)
set @i=@i+1
end
set @sql= 'select * from #oo '
exe(@sql)
drop table #oo
[解决办法]
create PROCEDURE db_owner.FinanceStat
@year int
AS
begin
declare @i int
declare @sql varchar(500)
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
insert into #oo(mon,raccount,caccount,account)
SELECT
@i,
@raccount=ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0),
@caccount=ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0),
@account=ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0)
where
status=0 and year(addtime)=@year and month(addtime)=@i
set @i=@i+1
end
set @sql= 'select * from #oo '
exe(@sql)
drop table #oo
end
[解决办法]
ALTER PROCEDURE db_owner.FinanceStat
@year int
AS
declare @i int
declare @raccount float
declare @caccount float
declare @account float
declare @sql varchar(500)
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
set @raccount=SELECT ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)=@year and month(addtime)=@i
SELECT @caccount=ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0) AS raccount where status=0 and year(addtime)=@year and month(addtime)=@i
SELECT @account=ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0) AS account where status=0 and year(addtime)=@year and month(addtime)=@i
insert into #oo(mon,raccount,caccount,account) values(@i,@raccount,@caccount,@account)
set @i=@i+1
end
set @sql= 'select * from #oo '
exe(@sql)
drop table #oo
[解决办法]
try
create PROCEDURE db_owner.FinanceStat
@year int,
@sql varchar(500) output
AS
declare @i int
declare @raccount float
declare @caccount float
declare @account float
set @i=1
create table #oo(mon int,raccount float,caccount float,account float)
while @i <13
begin
insert into #oo(mon,raccount,caccount,account)
Select
@i,
ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE 0 END), 0),
ISNULL(SUM(CASE type WHEN 1 THEN jine ELSE 0 END), 0),
ISNULL(SUM(CASE type WHEN 0 THEN jine ELSE - jine END), 0)
From account
where status=0 and year(addtime)=@year and month(addtime)=@i
set @i=@i+1
end
set @sql= 'select * from #oo '
exec(@sql)
drop table #oo
