在sql2000中 如何在游标后面的sql语句加上动态参数 急

[lijun_js]

GO
/****** 对象: StoredProcedure [dbo].[updateProductOrder] 脚本日期: 01/08/2011 11:51:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[updateOpenProductOrder]

@companyID int ,
@groupID int ,
@type int
AS
declare @productID int;
declare @orderNum int ;
declare @orderType varchar(500);

--产品型号升序
if(@type=1)
begin
set @orderType=' order by sModel asc ';
end

--产品货号降序
if(@type=2)
begin
set @orderType=' order by sModel desc ';
end

--产品名称降序
if(@type=3)
begin
set @orderType=' order by sProductName asc ';
end

-- 产品名称升序
if(@type=4)
begin
set @orderType=' order by sProductName desc ';
end

--产品更新时间升序
if(@type=5)
begin
set @orderType=' order by dLastUpdateTime asc ';
end

--产品更新时间降序
if(@type=6)
begin
set @orderType=' order by dLastUpdateTime desc ';
end

set @orderNum=1;
declare rs CURSOR LOCAL SCROLL FOR
select iProductID from Product_Info where iProductID in
(select iProductID from OpenRoomGroup_Product where iGroupID=@groupID )
and iCompanyID=@companyID || @orderType open rs
fetch NEXT FROM rs INTO @productID
WHILE @@FETCH_STATUS = 0
BEGIN
print(str(@orderNum)+'--'+str(@productID));

FETCH NEXT FROM rs INTO @productID;
set @orderNum=@orderNum+1;
end
CLOSE rs


存储过程已给出，就是红色的地方有错 ，请问如何修改，尝试好多方法了 在线等待 急 急！！

[playwarcraft]

用动态啊,sql语句串好

exec('declare rs cursor for select ...')

[lijun_js]

[Quote=引用 1 楼 playwarcraft 的回复:]

用动态啊,sql语句串好

exec('declare rs cursor for select ...')
[/Quote]

这位大侠 具体麻烦你一下 我不是清楚怎么该 你帮我改下 谢谢啦

[lijun_js]

[Quote=引用 1 楼 playwarcraft 的回复:]

用动态啊,sql语句串好

exec('declare rs cursor for select ...')
[/Quote]

Msg 16916, Level 16, State 1, Procedure updateOpenProductOrder, Line 58
名为 'rs' 的游标不存在。
Msg 16916, Level 16, State 1, Procedure updateOpenProductOrder, Line 61
名为 'rs' 的游标不存在。
Msg 16916, Level 16, State 1, Procedure updateOpenProductOrder, Line 69
名为 'rs' 的游标不存在。

这是执行的结果

[lijun_js]

GO
/****** 对象: StoredProcedure [dbo].[updateProductOrder] 脚本日期: 01/08/2011 11:51:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[updateOpenProductOrder]

@companyID int ,
@groupID int ,
@type int
AS
declare @productID int;
declare @orderNum int ;
declare @orderType nvarchar(500);
declare @sql nvarchar(2000);


--产品型号升序
if(@type=1)
begin
set @orderType='order by sModel asc ';
end

--产品货号降序
if(@type=2)
begin
set @orderType=' order by sModel desc ';
end

--产品名称降序
if(@type=3)
begin
set @orderType=' order by sProductName asc ';
end

-- 产品名称升序
if(@type=4)
begin
set @orderType=' order by sProductName desc ';
end

--产品更新时间升序
if(@type=5)
begin
set @orderType=' order by dLastUpdateTime asc ';
end

--产品更新时间降序
if(@type=6)
begin
set @orderType=' order by dLastUpdateTime desc ';
end


set @sql='declare rs CURSOR LOCAL SCROLL FOR select iProductID from Product_Info where iProductID in
(select iProductID from OpenRoomGroup_Product where iGroupID='+str(@groupID) +') and iCompanyID='+str(@companyID)+''+@orderType



print(@sql);

exec(@sql);

set @orderNum=1;

open rs
fetch NEXT FROM rs INTO @productID
WHILE @@FETCH_STATUS = 0
BEGIN
print(str(@orderNum)+'--'+str(@productID));

FETCH NEXT FROM rs INTO @productID;
set @orderNum=@orderNum+1;
end
CLOSE rs

我把存储过程改成这样还是 就是报

Msg 16916, Level 16, State 1, Procedure updateOpenProductOrder, Line 59
名为 'rs' 的游标不存在。
Msg 16916, Level 16, State 1, Procedure updateOpenProductOrder, Line 62
名为 'rs' 的游标不存在。
Msg 16916, Level 16, State 1, Procedure updateOpenProductOrder, Line 70
名为 'rs' 的游标不存在。

[华夏小卒]

可以先用动态语句把结果插入到临时表里，
下面在外面定义游标

如 :

set @sql='select iProductID into ##tb   --之前判断##tb 是否存在，以及考虑并发，用随机全局表名的问题，没考虑，自己改改
from Product_Info where iProductID in  
  (select iProductID from OpenRoomGroup_Product where iGroupID='+ltrim(@groupID) +') and iCompanyID='+ltrim(@companyID)+' '+@orderType

exec(@sql)

declare rs cursor for select * from ##tb 

......