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

Question

question

David 2 1 asked Dec 3, '09

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.

t-sql sql-server-2000 stored-procedures

10 |1200

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

Answer 1 · 2009-12-03T16:47:37Z

Kristen answered Dec 3, '09

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

10 |1200

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

Answer 2 · 2009-12-04T04:32:56Z

Madhivanan answered Dec 4, '09

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

10 |1200

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

Answer 3 · 2009-12-04T08:44:15Z

Kristen answered Dec 4, '09

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.

10 |1200

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

question