you must use dynamic cursor!for example:declare @comp_id varchar(3),@SQL varchar(4000) set @comp_id = '001' set @SQL = 'select TRANS_NO from TransMaster where COMP_ID = '+ @comp_id exec ('declare cur cursor for '+@SQL) open cur ......
see the example:CREATE PROCEDURE lake_list ( @region varchar(30), @size integer, @lake_list_cursor CURSOR VARYING OUTPUT ) AS BEGIN DECLARE @ok SMALLINT EXECUTE check_authority @region, username, @ok OUTPUT IF @ok = 1 BEGIN SET @lake_list_cursor =CURSOR LOCAL SCROLL FOR SELECT name, lat, long, size, boat_launch, cost FROM lake_inventory WHERE locale = @region AND area >= @size ORDER BY name OPEN @lake_list_cursor END END DECLARE @my_lakes_cursor CURSOR DECLARE @my_region char(30) SET @my_region = 'Northern Ontario' EXECUTE lake_list @my_region, 500, @my_lakes_cursor OUTPUT IF Cursor_Status('variable', '@my_lakes_cursor') <= 0 BEGIN /* Some code to tell the user that there is no list of lakes for him/her */ END ELSE BEGIN FETCH @my_lakes_cursor INTO -- Destination here -- Continue with other code here. END
leimin(黄山光明顶) 回复合乎我的问题,可是实现起来有问题,说我没有定义游标和里面的变量
可能您的游标没有声明或声明不合法,下面是个简单的例子: 使用简单游标和语法 打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR FOR SELECT * FROM authors OPEN authors_cursor FETCH NEXT FROM authors_cursor
set @comp_id = '001'
set @SQL = 'select TRANS_NO from TransMaster where COMP_ID = '+ @comp_id
exec ('declare cur cursor for '+@SQL)
open cur
......
( @region varchar(30),
@size integer,
@lake_list_cursor CURSOR VARYING OUTPUT )
AS
BEGIN
DECLARE @ok SMALLINT
EXECUTE check_authority @region, username, @ok OUTPUT
IF @ok = 1
BEGIN
SET @lake_list_cursor =CURSOR LOCAL SCROLL FOR
SELECT name, lat, long, size, boat_launch, cost
FROM lake_inventory
WHERE locale = @region AND area >= @size
ORDER BY name
OPEN @lake_list_cursor
END
END
DECLARE @my_lakes_cursor CURSOR
DECLARE @my_region char(30)
SET @my_region = 'Northern Ontario'
EXECUTE lake_list @my_region, 500, @my_lakes_cursor OUTPUT
IF Cursor_Status('variable', '@my_lakes_cursor') <= 0
BEGIN
/* Some code to tell the user that there is no list of
lakes for him/her */
END
ELSE
BEGIN
FETCH @my_lakes_cursor INTO -- Destination here
-- Continue with other code here.
END
回复合乎我的问题,可是实现起来有问题,说我没有定义游标和里面的变量
使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项,FETCH NEXT 是唯一可用的提取选项。DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor