0

I am trying to create the below dynamic update query with some variables and for some reason, it's not working inside the stored procedure. Can someone suggest to me where I am doing wrong and what's the best practice by avoiding the SQL Injection as well?

 DECLARE @SQL NVARCHAR(MAX) DECLARE @COLUMN1 NVARCHAR(10) DECLARE @COLUMN2 NVARCHAR(10) DECLARE @TABLENAME NVARCHAR(10) SET @SQL = 'UPDATE TL SET '+ @COLUMN1 + '= AB.COLUMN1,' + @COLUMN2 + '= AB.COLUMN2 FROM' + @TABLENAME + ' TL JOIN ABACUS AB ON TL.REF = AB.REF AND TL.SUBS = AB.SUBS WHERE ' + @COLUMN1 + ' IS NULL AND ' + @COLUMN2 +' IS NULL'; SET @COLUMN1 = (SELECT CONCAT('USER_ID', '8')) SET @COLUMN2 = (SELECT CONCAT('USER_ID', '6')) SET @TABLENAME = 'POLICYREF'; EXEC sys.sp_executesql @SQL, @TABLENAME, @COLUMN1, @COLUMN2; SET @TABLENAME = 'USERREF'; EXEC sys.sp_executesql @SQL, @TABLENAME, @COLUMN1, @COLUMN2;
Improve this question
5
  • 1
    CAST(8 AS CHAR(1)) can just be a string '8' or even a number 8 if you don't care about the string conversion. Commented Jun 18, 2021 at 22:07
  • Thanks @markschultheiss Updated the question with a string. Commented Jun 18, 2021 at 22:09
  • Define "not working". And you're missing a space here @COLUMN2 +'IS NULL'; Commented Jun 18, 2021 at 22:15
  • @DAVIDBROWNE-MICROSOFT Thanks, I have added the space now. It doesn't like if I do SET @TABLENAME = 'POLICYREF'. I got a syntax near 'POLICYREF'. Commented Jun 18, 2021 at 22:28
  • @jaimedrq Thanks, the other part '8' would change according to the other select statement. Commented Jun 18, 2021 at 22:29

2 Answers 2

Reset to default
3

You need dynamic SQL, not parameters. You can't parameterize column names or table names. So something like:

DECLARE @SQL NVARCHAR(MAX) DECLARE @COLUMN1 NVARCHAR(10) = 'USER_ID8' DECLARE @COLUMN2 NVARCHAR(10) = 'USER_ID6' DECLARE @TABLENAME NVARCHAR(10) = 'POLICYREF' SET @SQL = 'UPDATE TL SET '+ quotename(@COLUMN1) + '= AB.COLUMN1,' + quotename(@COLUMN2) + '= AB.COLUMN2 FROM ' + quotename(@TABLENAME) + ' TL JOIN ABACUS AB ON TL.REF = AB.REF AND TL.SUBS = AB.SUBS WHERE ' + quotename(@COLUMN1) + ' IS NULL AND ' + quotename(@COLUMN2) +' IS NULL'; EXEC (@SQL) SET @TABLENAME NVARCHAR(10) = 'USERREF' SET @SQL = 'UPDATE TL SET '+ quotename(@COLUMN1) + '= AB.COLUMN1,' + quotename(@COLUMN2) + '= AB.COLUMN2 FROM ' + quotename(@TABLENAME) + ' TL JOIN ABACUS AB ON TL.REF = AB.REF AND TL.SUBS = AB.SUBS WHERE ' + quotename(@COLUMN1) + ' IS NULL AND ' + quotename(@COLUMN2) +' IS NULL'; EXEC (@SQL)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I have asked a question to Mark above, can you guide me as well whether it's possible or not please?
If you are asking if you can reference a temp table in dynamic SQL, the answer is yes.
QUOTENAME is missing in many places, as is a space after FROM
1

Not a huge fan of this but, given that, create a stored procedure OR re-arrange to execute each after updating the @SQL, here is the stored procedure example: Note this is missing production level things like a transaction, TRY CATCH etc. and is only for an basic UNTESTED example

CREATE PROCEDURE dbo.MyFunQuery @SQL NVARCHAR(MAX), @COLUMN1 NVARCHAR(10), @COLUMN2 NVARCHAR(10), @TABLENAME NVARCHAR(10) AS BEGIN SET @SQL = 'UPDATE TL SET '+ @COLUMN1 + '= AB.COLUMN1,' + @COLUMN2 + '= AB.COLUMN2 FROM ' + @TABLENAME + ' AS TL JOIN ABACUS AS AB ON TL.REF = AB.REF AND TL.SUBS = AB.SUBS WHERE ' + @COLUMN1 + ' IS NULL AND ' + @COLUMN2 + ' IS NULL;'; EXECUTE ( @SQL ); END

--Now to call it:

DECLARE @COLUMN1 NVARCHAR(10) = 'USER_ID8', @COLUMN2 NVARCHAR(10) = 'USER_ID6'; EXECUTE dbo.MyFunQuery @COLUMN1, @COLUMN2, @TABLENAME='POLICYREF'; EXECUTE dbo.MyFunQuery @COLUMN1, @COLUMN2, @TABLENAME='USERREF';
Improve this answer

4 Comments

Thanks Mark. One question, would it be possible to use a temp table instead of a hardcoded table name 'ABACUS'. Basically, this ABACUS table is a User defined function which returns a temp table.
This might help on the UDF dba.stackexchange.com/q/86143/44556
Sorry, it's not quite the same. I have a UDF which returns a table. In my stored procedure, I declared a temp table and insert it to select from the UDF. When I used the temp table name I got an error saying declare the temp table
@Sri That is perhaps a new question "How to insert into a temp table from a UDF" with specific code for that instance example. Note you might be able to use a CTE but without specific code that is just supposition learn.microsoft.com/en-us/sql/t-sql/queries/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.