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

avatar image

David 2 1
732 54 58 65

(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

avatar image

Kristen ♦
2.2k 7 11 14

(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

avatar image

Kristen ♦
2.2k 7 11 14

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

answered Dec 04, 2009 at 04:32 AM

avatar image

Madhivanan
1.1k 2 5 9

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

x1066
x501
x455

asked: Dec 03, 2009 at 12:16 PM

Seen: 5884 times

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

Copyright 2016 Redgate Software. Privacy Policy