Fetch Statement in Exec Statement

[chaoma]

Hi,

I am having problem using FETCH cursor statement inside a EXEC statement. I need to use EXEC because there is a variable inside the FETCH statement. Thank you.

Here is a sample data:

SELECT 'A' AS LETTER INTO ALPHABET
UNION
SELECT 'B'
UNION
SELECT 'C'


Here is a query I like to execute:

DECLARE SET_ALPHA CURSOR FOR SELECT * FROM ALPHABET
OPEN SET_ALPHA

DECLARE @PrimaryKey VARCHAR(50)
FETCH NEXT FROM SET_ALPHA INTO @PrimaryKey--Change this

CLOSE SET_ALPHA
DEALLOCATE SET_ALPHA

Now I like to change SET_ALPHA cursor in the Fetch statement. I am going to leave OPEN, CLOSE, DEALLOCATE without using variable for testing purpose, because I know it works!! Here is the new query:

DECLARE SET_ALPHA CURSOR FOR SELECT * FROM ALPHABET
OPEN SET_ALPHA

DECLARE @PrimaryKey VARCHAR(50)
DECLARE @cursor VARCHAR(500)
SET @cursor ='SET_ALPHA'
DECLARE @FetchStatement VARCHAR(500)
SET @FetchStatement="FETCH NEXT FROM "+@cursor+" INTO @PrimaryKey"
PRINT @FetchStatement
EXEC (@FetchStatement)

CLOSE SET_ALPHA
DEALLOCATE SET_ALPHA

Here is the error message:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@PrimaryKey'.

Thank you for any assistance.

Chaoma

 

[chaoma]

I forgot to mention that I am using SQL 2000. Thanks.

 

[mrdenny]

You are getting the error because the variable @PrimaryKey is defined outside of your dynamic SQL but you are trying to use it within the dynamic SQL. You can't cross bounds like that.

The end result that you are looking for appears to be a dynamic cursor name. I'm wondering why? What's the end result that you are looking for?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[chaoma]

I want to write a store procedure that will accept any cursor name. Microsoft SQL 2000 cusor is global. If I want to call this store procedure 10 times almost simultaneouly, I need to have different cursors. I hope you understand me on this one.

Anyway, how would I use @PrimaryKey in EXEC environment, if it is out of boundary.

Thanks.

 

[SQLSister]

In the first place, cursors are bad, you shouldn't be using then at all most of the time. They are extremely bad for performance. If you are doing an insert, update, or delete action, do not use a cursor.

In the second place, cursors are specific to the scope of the sp unless you specify global. Multiple users calling the same sp simultaneously will not be using the same cursor. So don't specify global if you need to run the proc simultaneously.

"NOTHING is more important in a database than integrity." ESquared