这个存储过程咋修改呢?

[whwzxl]

下面这个存储过程在保存检查语法都没问题,但当我在查询分析器中运行
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

[zicxc]

这么多变量都没定义

[zicxc]

这样改试试

ALTER proc [dbo].[sp_vippointmerge]
@servername1 varchar(30),
@databasename1 varchar(30)

as
declare @table1 varchar(100)

begin

set nocount on;

set @table1=@servername1 + '.' + @databasename1 + '.dbo.vip_vip'

exec ('

declare @vip_scucode_scu varchar(30)
declare @vip_totalpoint float
declare @vip_couponpoint float
declare @vip_previouspoint float
declare @vip_previouscouponpoint float

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
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

[whwzxl]

将 declare @vip_scucode_scu varchar(30)
declare @vip_totalpoint float
declare @vip_couponpoint float
declare @vip_previouspoint float
declare @vip_previouscouponpoint float
移到exec内，还是提示
消息 137，级别 15，状态 2，第 3 行
Must declare the scalar variable "@vip_totalpoint".
但已经定义了啊，如何解决？