How do I return a cursor for dynamic SQL?

Use a GLOBAL cursor:

SET @SQLSTR= @SQLSTR + 'DECLARE C0 Cursor GLOBAL for Select AX_IAPCID from IRIS.XXXLIBXXX_ICOXREP WHERE AX_IAQQOX = @PAUSPR;'
SET @SQLSTR = REPLACE(@SQLSTR,'XXXLIBXXX',RTRIM(@LIBNAME))
SET @SQLPARAMS=N'@PAUSPR sysname'
EXEC sp_executesql @SQLSTR, @SQLPARAMS, @PAUSPR

'now, use the GLOBAL cursor declared using the dynamic sql above
OPEN C0
...
CLOSE C0
DEALLOCATE C0

Note: GLOBAL means global to the connection, so each connection will be able to have it's own cursor like this.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp

CHeers

I would recommend not using a cursor.
They are never necessary and usually a very bad idea in sql server.

A global cursor sounds good but unfortunately I now get an error which states:-

"A cursor with the name 'C0' does not exist"
when the open statement is run. It appears that the cursor declaration inside the sp_executesql is not being recognised. I've tried simplifying the SQL to a hard coded string identical to one which does work when declared directly (just to confirm that the declaration statement is OK) but the same error still occurs.

Any other ideas?

What version of SQL Server do you use?

This is on SQL/Server 2000 Enterprise Edition but I'd also need to be able to implement my solution on the Standard edition as well.

Curious, because I tested the above code with SQL 2000 and it worked fine.
CHeers

Hmmm... Very vexing! Could it be because I have some installation / configuration switch set incorrectly? I've just read about the default scope setting for undefined cursors but that shouldn't make any difference since I'm explicitly defining mine as global. Have also just tried defining a local cursor, setting it to the global cursor and using that instead, but this just results in the error I was getting originally.

One work round that's been suggested is to create a temporary table inside the sp_executesql and then define an explcit cursor over that (there isn't much data in the tables so this might not be TOO slow, although I dare say there'd be a performance hit even if I can get it to work.)

Alternatively I'm beginning to think I might have to find a way to get this procedure to work without cursors, but I've got a few more like this that I need to modify so it won't be a nice job to have to modify the existing logic :-(

Hmmm... Very vexing! Could it be because I have some installation / configuration switch set incorrectly? I've just read about the default scope setting for undefined cursors but that shouldn't make any difference since I'm explicitly defining mine as global. Have also just tried defining a local cursor, setting it to the global cursor and using that instead, but this just results in the error I was getting originally.

One work round that's been suggested is to create a temporary table inside the sp_executesql and then define an explcit cursor over that (there isn't much data in the tables so this might not be TOO slow, although I dare say there'd be a performance hit even if I can get it to work.)

Alternatively I'm beginning to think I might have to find a way to get this procedure to work without cursors, but I've got a few more like this that I need to modify so it won't be a nice job to have to modify the existing logic :-(

>installation / configuration switch set incorrectly
I don't think so...
I think however that the is probably a small typo or so that makes you code not working as expected. Can you copy the (full) code here, so we might check?
CHeers

OK, thanks. This is the complete text of my last attempt to run the stored procedure with all the gory details. It's modified from a version where the cursor was based over a fixed file name and the commented out cursor definitions are for the old fixed names. (I also know I'll need to change cursor C1 in the same way once I've proved I can get a dynamic cursor to work for C0) Using a cursor for C0 itself may be unnecessary but I think I'm going to need it for C1 and for my other procedures....

CREATE           PROCEDURE IRIS.UXSPXZ03
      @LIBNAME char(10),
      @PACNDT varchar(7),
      @PADUDT varchar(7),
      @PAAXRI varchar(1) output,
      @PAUSPR varchar(10),
      @PAERID varchar(64) output,
      @PAAUNO int output with recompile AS

--- Author DMFW 04/10/02
--- Obtain a valid audit number for closings
 
DECLARE @CONTROL_DATE char(10)
DECLARE @DUE_DATE char(10)
DECLARE @WORK_STATION char(10)
DECLARE @AUDIT_ERROR bit
DECLARE @DUPLICATE bit

DECLARE @AUTO_RI_LOWER_LIMIT int
DECLARE @AUTO_RI_UPPER_LIMIT int
DECLARE @NEXT_AUTO_RI_AUDIT_NUMBER int
DECLARE @GROSS_MANUAL_LOWER_LIMIT int
DECLARE @GROSS_MANUAL_UPPER_LIMIT int
DECLARE @NEXT_GROSS_MANUAL_AUDIT_NUMBER int

--- DECLARE C0 Cursor Global for Select AX_IAPCID from IRIS.DEMODATA_ICOXREP WHERE AX_IAQQOX = @PAUSPR;

---- DECLARE C0 Cursor for
----    Select AX_IAPCID from IRIS.DEMODATA_ICOXREP WHERE AX_IAQQOX = @PAUSPR;
-----DECLARE C1 Cursor for
----    Select AX_IAAXFM, AX_IAAXTZ, AX_IAAXNX, AX_IAIWFM, AX_IAIWTZ, AX_IAIWNX from IRIS.DEMODATA_ICVDREP WHERE AX_IAWSID = @WORK_STATION for update of AX_IAIWNX, AX_IAIWFM;

-------------------------------------------------------------

DECLARE @SQLSTR nvarchar(1000)
DECLARE @SQLPARAMS nvarchar(1000)

SET @SQLSTR= @SQLSTR + 'DECLARE C0 CURSOR GLOBAL for Select AX_IAPCID from IRIS.XXXLIBXXX_ICOXREP WHERE AX_IAQQOX = @NPARM;'
SET @SQLSTR = REPLACE(@SQLSTR,'XXXLIBXXX',RTRIM(@LIBNAME))
SET @SQLPARAMS=N'@NPARM varchar'
EXEC sp_executesql @SQLSTR, @SQLPARAMS, @NPARM = 'E#UW'

SET @PAERID=''
 
--- Ensure that the flag always contains a valid value
 
If @PAAXRI <> 'Y'
      SET @PAAXRI= 'N'

--- Convert the date into IRIS format.
--- On the AS/400 this is a call to some common Y2K string manipulation CL
--- but this can be simplified here to just append a '1' on the front as all
--- dates will be in the 21st Century.
 
SET @CONTROL_DATE='1' + @PACNDT
SET @DUE_DATE='1' + @PADUDT

Open C0

FETCH NEXT FROM C0 into @WORK_STATION

if @@FETCH_STATUS<>0
      begin
                SET @PAERID = 'AuditNo_NoWSID'
                RETURN
      end
   
close C0
deallocate C0

set @AUDIT_ERROR=0
set @PAAUNO=0
 
--- Loop round to find a valid audit number

WHILE (@PAAUNO <> 0) or (@AUDIT_ERROR = 1)
begin
      open C1
          fetch C1 into @AUTO_RI_LOWER_LIMIT, @AUTO_RI_UPPER_LIMIT, @NEXT_AUTO_RI_AUDIT_NUMBER, @GROSS_MANUAL_LOWER_LIMIT, @GROSS_MANUAL_UPPER_LIMIT, @NEXT_GROSS_MANUAL_AUDIT_NUMBER
   
          if @@FETCH_STATUS<>0
            begin
                        set @PAERID = 'AuditNo_Invalid'
                        set @AUDIT_ERROR=1
            end
          else
            --- Retrieve the next number and reset for next time.
            --- Either add 1 to latest value or if this will exceed
            --- the upper limit, reset to lower limit.
            if @PAAXRI = 'Y'
                    begin --- RI
                          if @NEXT_AUTO_RI_AUDIT_NUMBER < @AUTO_RI_UPPER_LIMIT
                                  set @NEXT_AUTO_RI_AUDIT_NUMBER = @NEXT_AUTO_RI_AUDIT_NUMBER + 1
                          else
                                  set @NEXT_AUTO_RI_AUDIT_NUMBER = @AUTO_RI_LOWER_LIMIT
           
                          update IRIS.DEMODATA_ICVDREP
                            set AX_IAAXNX = @NEXT_AUTO_RI_AUDIT_NUMBER
                            where current of C1

                  set @PAAUNO = @NEXT_AUTO_RI_AUDIT_NUMBER
            end
      else
                    begin --- Gross
                          if @NEXT_GROSS_MANUAL_AUDIT_NUMBER < @GROSS_MANUAL_UPPER_LIMIT
                                  set @NEXT_GROSS_MANUAL_AUDIT_NUMBER = @NEXT_GROSS_MANUAL_AUDIT_NUMBER + 1
                          else
                                  set @NEXT_GROSS_MANUAL_AUDIT_NUMBER = @GROSS_MANUAL_LOWER_LIMIT
     
                  update IRIS.DEMODATA_ICVDREP
                  set AX_IAIWNX = @NEXT_GROSS_MANUAL_AUDIT_NUMBER
                  where current of C1
       
                          set @PAAUNO = @NEXT_GROSS_MANUAL_AUDIT_NUMBER

                  end
       
      close C1
      deallocate C1

      --- validate with control date
          EXEC IRIS.ZZVRXZ0 @PACNDT, @PAAUNO, @DUPLICATE OUTPUT
          --- validate with due date
          if @DUPLICATE=0
                  EXEC IRIS.ZZVRXZ0 @PADUDT, @PAAUNO, @DUPLICATE OUTPUT
          

      if @DUPLICATE=1
                  --- This is not an error as such but we need to loop back to                  --- try the next number so reset the audit number to try again
                  set @PAAUNO=0

end


The complete error text I get when I run this in debug mode is:-

Server: Msg 16916, Level 16, State 1, Procedure UXSPXZ03, Line 59
[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'C0' does not exist.

Fantastic! That works fine now, thank you very much. That's what happens when you've been working in VB for too long (where strings are initialised to empty by default).