这个存储过程咋修改呢?

whwzxl 2006-08-12 11:18:25
下面这个存储过程在保存检查语法都没问题,但当我在查询分析器中运行
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
...全文
155 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
whwzxl 2006-08-14
  • 打赏
  • 举报
回复
将 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".
但已经定义了啊,如何解决?
zicxc 2006-08-12
  • 打赏
  • 举报
回复
这样改试试

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
zicxc 2006-08-12
  • 打赏
  • 举报
回复
这么多变量都没定义

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧