遊標變數用法經典

-- -產生測試表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