摘自SQLSERVER CREATE PROCEDURE帮助: USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P') DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS SET @titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titlesOPEN @titles_cursor GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs GO DECLARE @MyCursor CURSOR EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor GO
create proc procauthornames @curauthornames cusor varying output as set @curauthornames=cursor for select au_fname+' '+au_lanme from authors open @curauthornames 利用返回的游标 declare @curauthors cursor declare @varcharauthornames varchar(64) exec procauthornames @curauthors output fetch next from @curauthors into @varcharauthornames while (@@fetch_status=0) begin print @varcharauthornames fetch next from @curauthors into @varcharauthornames end close @curauthors deallocate @curauthors
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titlesOPEN @titles_cursor
GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
@curauthornames cusor varying output
as
set @curauthornames=cursor for
select au_fname+' '+au_lanme
from authors
open @curauthornames
利用返回的游标
declare @curauthors cursor
declare @varcharauthornames varchar(64)
exec procauthornames @curauthors output
fetch next from @curauthors into @varcharauthornames
while (@@fetch_status=0)
begin
print @varcharauthornames
fetch next from @curauthors into @varcharauthornames
end
close @curauthors
deallocate @curauthors