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

Question

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.

Answer 1

0

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 +''''

answered Dec 03 '09 at 04:47 PM

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 '09 at 06:53 AM David 2 1

Ooops sorry for the formatting.

Dec 04 '09 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 '09 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 '09 at 09:23 AM David 2 1

(comments are locked)

Answer 2

0

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.

answered Dec 04 '09 at 08:44 AM

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 '09 at 09:44 AM David 2 1

(comments are locked)

Answer 3

0

Refer this to know how single quotes work in SQL Server http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

answered Dec 04 '09 at 04:32 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

(comments are locked)

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

Follow this question

Related Questions