一个存储过程 请这会儿不忙的人看看有什么毛病
ALTER PROCEDURE [dbo].[RankMedicine]
(
@IPBID int,
@RaceTimes int
)
AS
SET NOCOUNT ON
declare @TempValue table (ID bigint identity (1,1),ValueID bigint)
insert into @TempValue (ValueID) select ValueID from TempValue where IPBID=@IPBID
Declare @PostMed table (ID bigint identity (1,1),PID bigint)
declare @i bigint
declare @TVCount bigint
--子循环中的变量声明
declare @ValueID bigint
declare @ii bigint
--declare @PCount bigint
declare @Pmpr1 money
declare @Pmpr2 money
declare @Pmpr3 money
declare @Rank1 int
declare @Rank2 int
declare @Rank3 int
declare @PID bigint
declare @getCount int
declare @sumPmpr money
declare @Coloms1Str varchar(50)
declare @Coloms2Str varchar(50)
if @RaceTimes=1
begin
set @Coloms1Str= 'PMPriceRelation1 '
set @Coloms2Str= 'Rank1 '
end
if @RaceTimes=2
begin
set @Coloms1Str= 'PMPriceRelation2 '
set @Coloms2Str= 'Rank2 '
end
if @RaceTimes=3
begin
set @Coloms1Str= 'PMPriceRelation3 '
set @Coloms2Str= 'Rank3 '
end
-------------------------------------------------
set @i=1
set @TVCount=(select count(*) from @TempValue)+1
while @i <@TVCount
BEGIN
set @ValueID=(select ValueID from @TempValue where ID=@i)
exec( 'select @sumPmpr=sum( '+@Coloms1Str+ ') from PostMedicine where valueid= '+@ValueID)
set @getCount=(select count(*) from PostMedicine where valueID=@ValueID)
update TempValue set finishedPrice=(@sumPmpr/@getCount) where valueID=@ValueID
exec( 'insert into @PostMed (PID) select PID from postmedicine where valueID= '+@ValueID+ ' order by '+@Coloms1Str+ ' asc ')
set @ii=1
while @ii <@getCount
Begin
set @PID=(select PID from @PostMed where ID=@ii)
exec( 'update PostMedicine set '+@Coloms2Str+ '=@ii where pid=@PID ')
set @ii=@ii+1
End
set @i=@i+1
END
select (@i-1)

=============================================
执行的时候老提示

必须声明标量变量 "@sumPmpr "。
必须声明表变量 "@PostMed "。
必须声明标量变量 "@ii "。

=============================
久思不得其解。。。


[解决办法]
你应该看一下sp_executesql 用sp_executesql 来执行动态的Sql语句.比如：

execute sp_executesql
N 'select * from pubs.dbo.employee where job_lvl = @level ',
N '@level tinyint ',
@level = 35

[解决办法]
因为exec的关系，exec是个函数，所以用exec执行sql语句的时候，sql 语句里的变量与存储过程中的定义两者是分开的，所以

exec( 'select @sumPmpr=sum( '+@Coloms1Str+ ') from PostMedicine where valueid= '+@ValueID)
就和直接在查询分析器里单独执行一样，会提示没@sumPmpr 变量