?
Solved

How do I return a cursor for dynamic SQL?

Posted on 2003-02-28
12
Medium Priority
?
460 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:DMFW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8041302
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
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8043740
I would recommend not using a cursor.
They are never necessary and usually a very bad idea in sql server.
0
 

Author Comment

by:DMFW
ID: 8056646
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?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8056716
What version of SQL Server do you use?
0
 

Author Comment

by:DMFW
ID: 8057574
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8057681
Curious, because I tested the above code with SQL 2000 and it worked fine.
CHeers
0
 

Author Comment

by:DMFW
ID: 8057905
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 :-(
0
 

Author Comment

by:DMFW
ID: 8063847
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 :-(
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8063915
>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
0
 

Author Comment

by:DMFW
ID: 8064076
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 8064665
Ok, found YOUR problem:

SET @SQLSTR= @SQLSTR + 'DECLARE C0 CURSOR GLOBAL for Select AX_IAPCID from IRIS.XXXLIBXXX_ICOXREP WHERE AX_IAQQOX = @NPARM;'

replace that line by this:
SET @SQLSTR= 'DECLARE C0 CURSOR GLOBAL for Select AX_IAPCID from IRIS.XXXLIBXXX_ICOXREP WHERE AX_IAQQOX = @NPARM;'

or use this before your line, to initialize the variable @SQLSTR:
SET @SQLSTR= ''

The problem is that without that initialization, @SQLSTR will be NULL (note: NULL+'yourstring' -> NULL !!!!)
And sp_executesql (and also exex) will execute a NULL string -> do nothing, not even "complain" about an empty string

CHeers
0
 

Author Comment

by:DMFW
ID: 8064863
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).
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question