x

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

more ▼

asked Dec 02, 2009 at 02:30 PM in Default

David 2 1 gravatar image

David 2 1
412 46 50 52

Can you give more informations on why you need to do this via cursor?
Dec 03, 2009 at 07:00 AM Madhivanan
(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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

more ▼

answered Dec 03, 2009 at 07:31 AM

David 2 1 gravatar image

David 2 1
412 46 50 52

Please note that you can accept your own answer (which will marked the question as "Answered")
Dec 03, 2009 at 09:33 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x476
x415
x59

asked: Dec 02, 2009 at 02:30 PM

Seen: 3214 times

Last Updated: Dec 03, 2009 at 09:34 AM