T-SQL Stored Procedure with Cursor Loop causing Errors

Question

Could someone please explain to me why I have error message show up when I exec the stored procedure. "A cursor with the name 'tName_cursor' already exists"

DECLARE @tName VARCHAR(100)
DECLARE @lsql VARCHAR(8000)  

DECLARE tName_cursor CURSOR FOR             
        SELECT NAME FROM SYS.tables WHERE TYPE = 'U' AND NAME LIKE 'PPM_METRIC%'
OPEN tName_cursor;
FETCH NEXT FROM tName_cursor INTO @tName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @lsql = N'
        UPDATE [' +@tName+ ']
           SET LATESTOFALL_FLG = ''N''
          FROM [' +@tName+ '] T 
          JOIN D_CUSTOM_METRICS_RULE  S
            ON T.METRIC_ID = S.CUSTOM_METRIC_RULE_ID
           AND T.LATESTOFALL_FLG = ''Y'''

    EXECUTE sp_executesql @lsql
    FETCH NEXT FROM tName_cursor INTO @tName;   
END
CLOSE tName_cursor;
DEALLOCATE tName_cursor;

DECLARE tName_cursor_REDO CURSOR FOR        
        SELECT NAME FROM SYS.tables WHERE TYPE = 'U' AND NAME LIKE 'PPM_METRIC%'
OPEN tName_cursor_REDO;
FETCH NEXT FROM tName_cursor_REDO INTO @tName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @lsql = N'      
            UPDATE [' +@tName+ ']
               SET LATESTOFDAY_FLG = ''N''
              FROM [' +@tName+ '] T 
              JOIN D_CUSTOM_METRICS_RULE  S
                ON T.METRIC_ID = S.CUSTOM_METRIC_RULE_ID
               AND T.CALC_METRIC_DATE_ID = CONVERT(INT,convert(VARCHAR, getdate(), 112))
               AND T.LATESTOFDAY_FLG = ''Y'''

    EXECUTE sp_executesql @lsql
    FETCH NEXT FROM tName_cursor_REDO INTO @tName;  
END
CLOSE tName_cursor_REDO;
DEALLOCATE tName_cursor_REDO;

Is this the whole stored procedure?, can you post the entire code with ALTER PROCEDURE ....? — Lamak
– Lamak, Commented May 16, 2014 at 16:30
Its not the whole stored procedure. But I believe this is the part causing the issue. — TDai
– TDai, Commented May 16, 2014 at 16:52
Well, I don't see a problem with the code, so it can be in the part that you left out — Lamak
– Lamak, Commented May 16, 2014 at 16:53

xacinay · Accepted Answer · 2014-05-16 17:15:25Z

2

Looks like some exception or 'return' breaks execution before DEALLOCATE command

"A cursor with the name 'tName_cursor' already exists"

message can happen when your 'declare' block is executed successfully, but after some code fails before 'DEALLOCATE tName_cursor' statement. Then, the second time you execute stored proc, it tries to declare a cursor again and the error message pops out. I recommend you to add 'begin try .. end try' block to your code and 'print ERROR_MESSAGE()' in exception block to see what comes out.

edited May 16, 2014 at 17:15

answered May 16, 2014 at 17:08

xacinay

9092 gold badges11 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lamak Over a year ago

This is actually good advice. Also look for different instances running the same sp in parallel

TDai Over a year ago

Thanks for the advice. Inititally, I was wrapping this inside a BEGIN TRYTRY and BEGIN TRAN statement.

Moe Sisko · Accepted Answer · 2014-05-19 00:26:40Z

0

It sounds like you may be using GLOBAL cursors (?). If you don't need them, I suggest using LOCAL cursors. e.g.

DECLARE tName_cursor_REDO CURSOR LOCAL FOR        
        SELECT NAME FROM SYS.tables WHERE TYPE = 'U' AND NAME LIKE 'PPM_METRIC%'

That may help to make things more robust.

answered May 19, 2014 at 0:26

Moe Sisko

12.2k9 gold badges52 silver badges88 bronze badges

Collectives™ on Stack Overflow

T-SQL Stored Procedure with Cursor Loop causing Errors

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related