How do I create cursor names from variables

[redgate]

Hi All

Even though the use of cursors is not popular I would like to query a multi level structure. The level count will define how many cursors I will need.

If I replace STR(@CNT) with for example C1 this works fine.

DECLARE @CNT INT
DECLARE @LVL INT

SET @LVL = 3
SET @CNT = 1

WHILE (@CNT <= @LVL)
BEGIN

DECLARE STR(@CNT) CURSOR FOR

SELECT BID FROM TBLREL

DEALLOCATE STR(@CNT)

SET @CNT = (@CNT + 1)
END

Ignore the select statement as this probably seems pointless.

Thanks in advance

 

[rt63]

I do not think it is possible to dynamically name a cursor. There could be work arounds to your requirement of mulitple levels depending on what the cursor is expected to output. In your code, you have given a fixed column name for all levels which obviously will not be the case in actual code. If you can give some actual code/rqrmnts, maybe an alternative can be figured out. RT

 

[redgate]

Thanks for the response rt63.

I have a maximum number of levels in my system. So I know where the limit is. I am setting a counter to track which level I am on and using a case statement to determine which cursor name to use. I thought it would have been neater to dynamically assign these cursors.

regards

 

[sunila7]

Hi,

Try this code.... actually u will to do using dynamically execute SQL queries....

DECLARE @CNT INT
DECLARE @LVL INT
Declare @SQL varchar(200)
SET @LVL = 3
SET @CNT = 1

WHILE (@CNT <= @LVL)
BEGIN

SET @SQL = 'DECLARE cursor' + Convert(varchar(10),@CNT) + ' CURSOR FOR SELECT top 1 * FROM charge Open cursor' + Convert(varchar(10),@CNT)
-- print @SQL
Execute (@SQL)
---- Here cursor is open with names cursor1,cursor2,cursor3 after each loop
---- So this cursors can be used....
SET @SQL = 'DEALLOCATE cursor' + Convert(varchar(10),@CNT)
execute (@SQL)
SET @CNT = (@CNT + 1)
END


Sunil

 

[redgate]

Excellent I will try this. Will respond ASAP.

Regards