Execute Dynamic SQL with Concated Variables

Question

I have multiple columns with names like "abc", "abc1", "abc2", "abc3". My goal is to push the value to the next column, so:

abc3 = abc2
abc2 = abc1
abc1 = abc
abc = null

I wrote this:

DECLARE @name varchar(20)
SET @name = 'abc'

DECLARE @exec nvarchar(max)
SET @exec = 
    'UPDATE ' + @tbl + '
        SET ' + @name + '3 = ' + @name + '2,
        ' + @name + '2 = ' + @name + '1,
        ' + @name + '1 = ' + @name + ',
        ' + @name + '= ''''

EXEC sp_executesql @exec

But if I want to change the name "abc" in something else and execute the query once again, the name does not update.

I found a way to assign the value within the sp_executesql command, however I'm not able to concat the namevalue with the numbers that way.

Is it possible to create a query like that, where I can execute it multiple times with different names?

My idea is to run it like that and change the names there.

EXEC sp_executesql @exec, N'@name nvarchar(20)', @name = N'abc'
EXEC sp_executesql @exec, N'@name nvarchar(20)', @name = N'xyz'

You have a problem with your data model. This operation should not be desired in any application. — Gordon Linoff, Feb 03 '21 at 13:34
why can you not concat the name and number? Do you get an error? If so what is it? I can run your query and print out the @exec value and it looks fine to me (but can not test without your table structures or anything. — Brad, Feb 03 '21 at 13:40
object names cannot be parameterized - including table and column. — SMor, Feb 03 '21 at 13:42
*Never* inject unsanitised strings into a dynamic statement. `QUOTENAME` exists for a reason; properly quote your objects and **parametrise** your calls to `sys.sp_executesql` don't inject the values (in this case, the value of `@name` on the otherside of the `=`. — Thom A, Feb 03 '21 at 13:42

score 2 · Answer 1 · answered Feb 03 '21 at 13:52

This, honestly, is a really odd requirement. It looks like the real problem is your design as it's denormalised (there should only be 1 name column, not 4).

Anyway, you can achieve this with dynamic SQL yes. Note you need to ensure you properly quote your dynamic objects, and parametrise values that aren't dynamic (such as your missing WHERE). This handles the safe injection, but it's ugly as anything...

DECLARE @schema sysname, --The data type used for object names. Synonym for nvarchar(128) NOT NULL
        @table sysname,
        @name sysname; 
--Assign @schema and @table too
SET @Name = N'abc';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'UPDATE ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@table) + @CRLF +
           N'SET ' + QUOTENAME(@name) + N' = '''',' + @CRLF +
           N'    ' + QUOTENAME(@name + '1') + N' = ' + QUOTENAME(@name) + N',' + @CRLF +
           N'    ' + QUOTENAME(@name + '2') + N' = ' + QUOTENAME(@name + '1') + N',' + @CRLF +
           N'    ' + QUOTENAME(@name + '3') + N' = ' + QUOTENAME(@name + '2') + N';';
           --No WHERE?

--PRINT @SQL --Your Best Friend
EXEC sys.sp_executesql @SQL;

Thanks for your help, but I feel like this is just a (much) better way of writing my already existing query. I am trying to now do exactly that, but with a way of changing the variable "@name" to run it multiple times without copying the whole thing over and over again. I do know it's a weird use-case, but I kinda need it that way as of now. Is there a way to run this with a set list of different names or something like "EXEC sys.sp_executesql @SQL, @name" after setting a different name again? (btw, I do have a WHERE statement, just deleted it while abstracting my question) — brainlordy, Feb 03 '21 at 14:33
It sounds like you're moving the goal posts here, @brainlordy . If you have a new question, you should post a new one. — Thom A, Feb 03 '21 at 14:36

Execute Dynamic SQL with Concated Variables

1 Answers1