x

How To Update A Dynamic SQL Table With A Set Variable?

Below I am trying to update a table with the variable @addflag however instead of updating the table with the required set variable it is inserting nulls. Everything else in the statement works apart from the update section. Can anyone help explain how I can get the table to update?

                    
create procedure loadcode @tabname varchar(50)                      
as                      
SET NOCOUNT ON                      
DECLARE @tabcur varchar(8000)                      
DECLARE @tabload varchar(8000)                      
DECLARE @tabup varchar(8000)                      
DECLARE @client VARCHAR(10)                      
DECLARE @addflag VARCHAR(10)                      
DECLARE @title VARCHAR(15)                      
SELECT @tabcur =    'DECLARE cur1 CURSOR FAST_FORWARD FOR ' +                      
    			'SELECT client,                      
    			title                      
    			FROM ' + @tabname + 'TBL'                      
OPEN cur1                      
FETCH NEXT FROM cur1                      
INTO @client, @title                      
WHILE @@FETCH_STATUS = 0                      
BEGIN                      
    IF @client = 12692 SET @addflag='C'                      
    ELSE IF @client = 1600031 SET @addflag='A'                      
    ELSE IF @client = 1590249 SET @addflag='R'                      
    ELSE SET @addflag='X'                       
    SELECT @tabload = 'INSERT INTO ' + @tabname + 'CODE (client,name)                       
    select client,title from ' + @tabname + 'TBL'                      
    SELECT @tabup = 'UPDATE ' + @tabname + 'CODE                      
    SET addflag =' + @addflag +''      -- THIS UPDATE IS NOT WORKING HERE                      
    FETCH NEXT FROM cur1                      
    INTO @client, @title                      
END                      
CLOSE cur1                      
DEALLOCATE cur1                      
                    
EXEC (@tabcur)                      
EXEC (@tabload)                       
EXEC (@tabup)                      

Thanks in advance.

more ▼

asked Dec 03, 2009 at 12:16 PM in Default

David 2 1 gravatar image

David 2 1
412 45 50 52

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You need to put single quotes around addFlag in your string concatenation, and as they are themselves within a string you will have to double them up. Hopefully this:


SELECT @tabup = 'UPDATE ' + @tabname + 'CODE
SET addflag =''' + @addflag +''''
more ▼

answered Dec 03, 2009 at 04:47 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

Thanks. That runs the query but inserts 'X' for ALL the records:

CLIENT ADDFLAG NAME
-------------------------------
12692 X Mr
1600031 X Mrs
1598548 X Mr
1468073 X Miss

Is my IF THEN ELSE outwith the scope of the update??
Dec 04, 2009 at 06:53 AM David 2 1
Ooops sorry for the formatting.
Dec 04, 2009 at 06:54 AM David 2 1
I thought that would probably be the case when I looked at the code, but wasn't sure. Your "IF @client" logic is OUTSIDE the actual cursor so the final ELSE will ALWAYS be true : ), so all that code would need to be part of the dynamic SQL I'm afraid ...
Dec 04, 2009 at 08:39 AM Kristen ♦
Thanks. Do you have an example you could provide? I just seem to be going round in circles with incorerect syntax errors. :((
Dec 04, 2009 at 09:23 AM David 2 1
(comments are locked)
10|1200 characters needed characters left

I think you are going to need something like:

            
SELECT @tabload = 'INSERT INTO ' + @tabname + 'CODE (client,name)               
    select client,title from ' + @tabname + 'TBL'              
    SELECT @tabup = 'UPDATE ' + @tabname + 'CODE              
    SET addflag =CASE @client WHEN 12692 THEN ''C'' WHEN 1600031 THEN ''A'' '            
...            

these things are notoriously difficult to debug, so if you can find a different solution I think that would be best.

I would put a PRINT or SELECT statement in place of the INSERT / UPDATE, initially, so you get some OUTPUT of what the INSERT / UPDATE would actually do, and then you can debug that until it is doing what you want, then chagne it to the INSERT / UPDATE commands.

more ▼

answered Dec 04, 2009 at 08:44 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

Thanks. I haven't yet considered CASE because the code I have given above is trimmd down from what the final will be as there's going to be a lot of: IF .... AND ... AND ... SET variable ELSE ....

Can CASE handle such queries?
Dec 04, 2009 at 09:44 AM David 2 1
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Dec 04, 2009 at 04:32 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x986
x473
x411

asked: Dec 03, 2009 at 12:16 PM

Seen: 4069 times

Last Updated: Dec 03, 2009 at 04:44 PM