游标变量用法经典

---生成测试表T
ifexists(select1fromsysobjectswhereName=N'T'andobjectProperty(ID,N'IsUserTable')=1)
droptableT
go
selecttop5ID,NameintoTfromsysobjects
go
方法1:

--创建输出游标变量的存储过程:

createprocedureP_cursor(
@Roy_Testcursorvaryingoutput
)
as

set@Roy_Test=cursorglobalfor
select
ID,Name
from
T
open@Roy_Test
if@@error<>0
return1
go

--调用的存储过程:

createprocedureP_cursor2
as
declare@Roy_Testcursor,
@IDint,@Namesysname

execP_cursor@Roy_Test=@Roy_Testoutput

IFCursor_Status('variable','@Roy_Test')<=0
return1

fetchnextfrom@Roy_Testinto@ID,@Name
while@@fetch_status=0
begin
print'ID='+rtrim(@ID)+',Name='+@Name
fetchnextfrom@Roy_Testinto@ID,@Name
end
close@Roy_Test
deallocate@Roy_Test
go


execP_cursor2--查看结果

/**//*
ID=1,Name=sysobjects
ID=2,Name=sysindexes
ID=3,Name=syscolumns
ID=4,Name=systypes
ID=6,Name=syscomments

*/

go
createprocedureP2_cursor(@Flagbit)
as
declare@roy_cursorcursor,
@IDint,@Namesysname
if@Flag=0
set@roy_cursor=Roy_cursor
else
return1--可以定义其它游标
open@roy_cursor
fetch@roy_cursorinto@ID,@Name
while@@fetch_status=0
begin
print'ID='+rtrim(@ID)+',Name='+@Name
fetch@roy_cursorinto@ID,@Name
end
close@roy_cursor
if@Flag=0
deallocateRoy_cursor
else
return1----可以定义其它游标

go
createprocedureP2_cursor2
as
exec('declareRoy_cursorcursorglobalfor
selectID,NamefromT')
declare@errorint
set@error=@@error
if@error=0
execP2_cursor@Flag=0
else
return@error
go

execP2_cursor2--查看结果

/**//*
ID=1,Name=sysobjects
ID=2,Name=sysindexes
ID=3,Name=syscolumns
ID=4,Name=systypes
ID=6,Name=syscomments

*/


删除测试:
--droptableT
--dropprocP_cursor,P_cursor2,P2_cursor,P2_cursor2