How do I return a cursor for dynamic SQL?
Posted on 2003-02-28
I have a stored procedure where I need to use some dynamic SQL because the name of the table I need to query is not known until run time. I also need a cursor from this SQL. The TransactSQL I am attempting to use looks like this:-
SET @SQLSTR= @SQLSTR + 'DECLARE C0 Cursor for Select AX_IAPCID from IRIS.XXXLIBXXX_ICOXREP WHERE AX_IAQQOX = @PAUSPR;'
SET @SQLSTR= @SQLSTR + 'SET @C0 = C0'
SET @SQLSTR = REPLACE(@SQLSTR,'XXXLIBXXX',RTRIM(@LIBNAME))
SET @SQLPARAMS=N'@C0 cursor output, @PAUSPR varchar'
EXEC sp_executesql @SQLSTR, @SQLPARAMS, @C0 output, @PAUSPR = @PAUSPR
SET @C1 = @C0
(@C0 and @C1 are declared as simple cursors without a from clause)
Although this code appears to execute without a problem, when the cursor @c1 is opened the following error message occurs:-
The variable '@C1' does not currently have a cursor allocated to it.
What am I doing wrong?