question

David 2 1 avatar image
David 2 1 asked

How To Create A Cursor That Uses Dynamic SQL?

I am trying to create a cursor that is uses dynamic SQL however the below procedure is erroring with "variable assignment is not allowed in a cursor declaration."

                    
create procedure loadcode @tabname varchar(50)                      
as                      
                    
DECLARE @tabload varchar(8000)                      
DECLARE @client VARCHAR(10)                      
DECLARE cur1 CURSOR FAST_FORWARD FOR                      
SELECT @tabload = 'SELECT client FROM ' + @tabname + '__TEXT'                    
                    
OPEN cur1                      
FETCH NEXT FROM cur1                      
INTO @client                      
WHILE @@FETCH_STATUS = 0                      
BEGIN                      
    PRINT + @client                      
    FETCH NEXT FROM cur1                      
    INTO @client                      
END                      
CLOSE cur1                      
DEALLOCATE cur1                    
                    
EXEC (@tabload)                      

I need to run an IF THEN ELSE insert into another table from the cursors output so how can I create this cursor? And if I was to use a WHILE loop how could it be coded?

TIA

t-sqlsql-server-2000stored-procedurescursor
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you give more informations on why you need to do this via cursor?
0 Likes 0 ·

1 Answer

· Write an Answer
David 2 1 avatar image
David 2 1 answered

Got it to work. For the record you need to declare the whole cursor from within dynamic sql.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.