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 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'' ' ... Problem with SP_MSFOREACHDB in OPENROWSET Trouble Creating Procedure With While Loop How To Create A Stored Procedure To Create A Table Passing Table Name In As A Parameter Can I call a stored procedure as part of a statement?
these things are notoriously difficult to debug, so if you can find a different solution I think that would be best. How To Return Only 1 Entry Per Row When A Column Can Contain Multiples which is the best way of approach stored procedure to take data from query SP taking more than 1 mintue to execute.
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.
TOP ( ) Specifies one or more table hints that are allowed for a target table. @ When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter ' N'.
If ' N' is not specified, SQL Server converts the string to the code page that corresponds to the default collation of the database or column.Specifies the temporary named result set or view, also known as common table expression (CTE), defined within the scope of the UPDATE statement.The CTE result set is derived from a simple query and is referenced by UPDATE statement.-- Does **NOT** fire from primary stored procedure (dictated by Update Record Date). BEGIN -- Table variable for storing **multi-row updates** -- Using scalar variables will not work! DECLARE @CHECKERS AS TABLE ( Order Num INT, Old_DCB INT, New_DCB INT, Old_MC INT, New_MC INT, Old_P2 NUMERIC(28,3), New_P2 NUMERIC(28,3), Old_PQ NUMERIC(28,3), New_PQ NUMERIC(28,3), Old_QT NUMERIC(28,3), New_QT NUMERIC(28,3) ) -- Old & new values populated to table variable. Final Edit - Solved - Solution Below I can't believe that I didn't know this, but apparently the issue was that the table variable and the real table both had the same name: @CHECKERS and dbo. I'm not an expert (clearly), and am always happy to receive constructive feedback. I hope this post helps someone out there avoid a headache...