这个存储过程咋修改呢?
下面这个存储过程在保存检查语法都没问题,但当我在查询分析器中运行
exec sp_vippointmerge 'dserver','database1'(传递参数为服务器名和数据库)
这时会提示下列错误:
消息 137,级别 15,状态 2,第 3 行
Must declare the scalar variable "@vip_totalpoint".
但我确实已经定义了参数@vip_totalpoint了啊,哪位能帮忙看看?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[sp_vippointmerge]
@servername1 varchar(30),
@databasename1 varchar(30)
as
declare @table1 varchar(100)
declare @vip_scucode_scu varchar(30)
declare @vip_totalpoint float
declare @vip_couponpoint float
declare @vip_previouspoint float
declare @vip_previouscouponpoint float
begin
set nocount on;
set @table1=@servername1 + '.' + @databasename1 + '.dbo.vip_vip'
exec ('declare cur_vip_vip cursor for
select vip_scucode_scu,
sum(vip_totalpoint)-max(vip_previouspoint) as vip_totalpoint,
sum(vip_couponpoint)-max(vip_previouscouponpoint) as vip_couponpoint,
sum(vip_totalpoint)-max(vip_previouspoint) as vip_previouspoint,
sum(vip_couponpoint)-max(vip_previouscouponpoint) as vip_previouscouponpoint
from
(select vip_scucode_scu,vip_totalpoint,vip_couponpoint,vip_previouspoint,vip_previouscouponpoint
from ' + @table1 + '
where vip_totalpoint<>vip_previouspoint or vip_couponpoint<>vip_previouscouponpoint
union all
select vip_scucode_scu,vip_totalpoint,vip_couponpoint,vip_previouspoint,vip_previouscouponpoint
from vip_vip
where vip_totalpoint<>vip_previouspoint or vip_couponpoint<>vip_previouscouponpoint) a
group by a.vip_scucode_scu
order by a.vip_scucode_scu')
open cur_vip_vip
fetch next from cur_vip_vip into
@vip_scucode_scu,@vip_totalpoint,@vip_couponpoint,@vip_previouspoint,@vip_previouscouponpoint
while @@fetch_status=0
begin
begin tran
exec('update ' + @table1 + '
set
vip_totalpoint=@vip_totalpoint,
vip_couponpoint=@vip_couponpoint,
vip_previouspoint=@vip_previouspoint,
vip_previouscouponpoint=@vip_previouscouponpoint
where vip_scucode_scu=@vip_scucode_scu')
update vip_vip
set
vip_totalpoint=@vip_totalpoint,
vip_couponpoint=@vip_couponpoint,
vip_previouspoint=@vip_previouspoint,
vip_previouscouponpoint=@vip_previouscouponpoint
where vip_scucode_scu=@vip_scucode_scu
IF @@ERROR = 0
begin
commit
end
else
begin
rollback
end
fetch next from cur_vip_vip into
@vip_scucode_scu,@vip_totalpoint,@vip_couponpoint,@vip_previouspoint,@vip_previouscouponpoint
end
close cur_vip_vip
deallocate cur_vip_vip
end