---產生測試表T
if exists(select 1 from sysobjects where Name=N'T' and objectProperty(ID,N'IsUserTable')=1)
drop table T
go
select top 5 ID,Name into T from sysobjects
go
方法1:
--建立輸出遊標變數的預存程序:
create procedure P_cursor(
@Roy_Test cursor varying output
)
as
set @Roy_Test=cursor global for
select
ID,Name
from
T
open @Roy_Test
if @@error<>0
return 1
go
--調用的預存程序:
create procedure P_cursor2
as
declare @Roy_Test cursor,
@ID int,@Name sysname
exec P_cursor @Roy_Test=@Roy_Test output
IF Cursor_Status('variable', '@Roy_Test') <= 0
return 1
fetch next from @Roy_Test into @ID,@Name
while @@fetch_status=0
begin
print 'ID='+rtrim(@ID)+',Name='+@Name
fetch next from @Roy_Test into @ID,@Name
end
close @Roy_Test
deallocate @Roy_Test
go
exec P_cursor2 --查看結果
/**//*
ID=1,Name=sysobjects
ID=2,Name=sysindexes
ID=3,Name=syscolumns
ID=4,Name=systypes
ID=6,Name=syscomments
*/
go
create procedure P2_cursor(@Flag bit)
as
declare @roy_cursor cursor,
@ID int,@Name sysname
if @Flag=0
set @roy_cursor=Roy_cursor
else
return 1 --可以定義其它遊標
open @roy_cursor
fetch @roy_cursor into @ID,@Name
while @@fetch_status=0
begin
print 'ID='+rtrim(@ID)+',Name='+@Name
fetch @roy_cursor into @ID,@Name
end
close @roy_cursor
if @Flag=0
deallocate Roy_cursor
else
return 1 ----可以定義其它遊標
go
create procedure P2_cursor2
as
exec ('declare Roy_cursor cursor global for
select ID,Name from T')
declare @error int
set @error=@@error
if @error=0
exec P2_cursor @Flag=0
else
return @error
go
exec P2_cursor2 --查看結果
/**//*
ID=1,Name=sysobjects
ID=2,Name=sysindexes
ID=3,Name=syscolumns
ID=4,Name=systypes
ID=6,Name=syscomments
*/
刪除測試:
--drop table T
--drop proc P_cursor,P_cursor2,P2_cursor,P2_cursor2
