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"。=============================
久思不得其解
(
@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"。=============================
久思不得其解
解决方案 »
- siteMapPath怎样与Web.sitemap关联?
- State问题
- 关于textbox的TextChanded事件
- 我的Panel里面控件是动态加载的,怎么foreach遍历获得控件值???
- DataList中,这种分页效果怎么做?
- vs 2005 上如何寫一個web Crystal Reports ?
- 关于2.0中的数据库连接异常的判断是如何做的
- 应用程序如何修改数据库?
- 数据层、逻辑层对数据库连接字符串调用如何统一使用表现层网站项目中的唯一Web.config中的定义
- 添加數據時出錯
- 动态增减dropdownlist控件的问题
- asp.net 导出excel文件有问题!!不能读取文件
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
exec('select @sumPmpr=sum('+@Coloms1Str+') from PostMedicine where valueid='+@ValueID)
就和直接在查询分析器里单独执行一样,会提示没@sumPmpr 变量