question

David 2 1 avatar image
David 2 1 asked

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-sqlsql-server-2000stored-procedures
10 |1200 characters needed characters left characters exceeded

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

Kristen avatar image
Kristen answered

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 characters needed characters left characters exceeded

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

Madhivanan avatar image
Madhivanan answered
10 |1200 characters needed characters left characters exceeded

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

Kristen avatar image
Kristen answered

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 characters needed characters left characters exceeded

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.