Accessing Global Cursor

hi friends,

Here is the stored procedures that I used.

--------------------------
create procedure globalCursor
AS
DECLARE abc CURSOR GLOBAL FOR
select * from sales
OPEN abc

create procedure globalCursorTest
AS
DECLARE @.sdate datetime
DECLARE @.sperson varchar(15)
DECLARE @.sregion varchar(15)
DECLARE @.sales int
EXECUTE globalCursor
FETCH NEXT FROM abc INTO @.sdate, @.sperson, @.sregion, @.sales
print @.sdate
print @.sperson
print @.sregion
print @.sales
--------------------------

When I execute globalCursorTest using SQL Query Analyser, it says

--------------------------
Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
A cursor with the name 'abc' already exists.
Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
The cursor is already open.
--------------------------

how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.

JakeLooks like the abc cursor is not closed/deallocated. Does either one of the procedures perform these actions?|||Ummm...

Do you have an Oracle background?

To my knowledge it doesn't work that way, though I'll go test it out...

And yes, as Kaiowas points out you need to

CLOSE ABC
DEALLOCATE ABC

But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||hi brett,

I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.

The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.

I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.

Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.

Appreciate your he
Jake

Originally posted by Brett Kaiser
Ummm...

Do you have an Oracle background?

To my knowledge it doesn't work that way, though I'll go test it out...

And yes, as Kaiowas points out you need to

CLOSE ABC
DEALLOCATE ABC

But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||anybody know about this....

Originally posted by Jake K
hi brett,

I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.

The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.

I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.

Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.

Appreciate your he
Jake|||"In DB2, one procedure can access the cursors opened by another procedure, after calling it."

Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||I guess my best suggestion would be to rewrite your cursor procedure as a table function.|||hi,

it's definitely not scope disaster!!! By default, the cursors opened in a procedure could not be accessed from another procedure. If one wants this kind of feature, the cursor has to be specially declared with the option "WITH RETURN TO CALLER/CLIENT". It's like Sequel's local & global cursor concept. In global cursor, the cursor can be accessed from outside where it is declared.

Jake

Originally posted by blindman
"In DB2, one procedure can access the cursors opened by another procedure, after calling it."

Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||thanks for your suggestion. as of now, i don't know about table function. I will try it out...
but i have another way of achieving this. the procedure that i attached in the starting mail is working fine, of course after including close & disallocate stmts at the end of the second procedure, globalCursorTest.
previously i used SQL Query Analyser GUI which will compile & execute the procedure at one shot. Thus the globalCursor procedure executed twice, which caused the 'cursor already opened' error.
As I mentioned in my earlier mails, i search for a mechanism which will only compile & create the procedure into the db without executing it. I find isql command line tool creates the procedure into the db without executing it.
After creating both the procedures, i executed second procedure, globalCursorTest. It works fine.

friends, Thanks for your time.

Jake

Originally posted by blindman
I guess my best suggestion would be to rewrite your cursor procedure as a table function.