SybaseBlog

You can use variables in cursor declarations, along these lines:


1. declare a cursor, using a variable
2. open that cursor, and use it
3. close that cursor
4. change the variable used in the declaration
5. open the cursor, and use it
6. close that cursor
(repeat 4-6 as you wish)



Here is an example.


dump tran sybsystemprocs with truncate_only
go


use sybsystemprocs
go


-- prepare the tests
if exists (select * from sysobjects where name = "TableA" and type = 'U')
drop table TableA
go
create table TableA (a int, b int)
go
insert into TableA values (1, 1)
go
insert into TableA values (2, 2)
go


if exists (select * from sysobjects where name = "sp_testcursor" and type = 'P')
drop proc sp_testcursor
go


-- create test procedure
create procedure sp_testcursor
as


declare @acol int
declare @bcol int


-- Step #1: create a cursor on a variable, and fetch
select @bcol = 1
declare var_cursor cursor for select a from TableA where b = @bcol for read only
open var_cursor
fetch var_cursor into @acol
while @@sqlstatus = 0
begin
print '*** Cursor fetched %1!, Expecting 1 ***', @acol
fetch var_cursor into @acol
end
close var_cursor


-- Step #2: change the variable, re-open the cursor, and fetch
select @bcol = 2
open var_cursor
fetch var_cursor into @acol
while @@sqlstatus = 0
begin
print '*** Cursor fetched %1!, Expecting 2 ***'', @acol
fetch var_cursor into @acol
end
go


if object_id('sp_testcursor') is not null
begin
print '*** Created procedure dbo.sp_testcursor ***'
grant execute on dbo.sp_testcursor to public
end
else
begin
print '*** Failed creating proc dbo.sp_testcursor ***'
end
go


-- do your tests
exec sp_testcursor
go


-- clean up
print '*** Deleting proc dbo.sp_testcursor ***'
if exists (select * from sysobjects where name = "sp_testcursor" and type = 'P')
drop proc sp_testcursor
go


print '*** Dropping table tableA ***'
drop table TableA
go