T-SQL variable not used in sp_execsql-CodePudding

I am running a cursor that executes dynamic SQL using a variable.

SET @Wk = CAST(@Cntr AS nvarchar(5))
DECLARE @params nvarchar(30) = N'@Wk nvarchar(5)'

--  .. start cursor

EXEC sp_executesql N'ALTER TABLE #Temp DROP COLUMN [WK @WK Sold]', @params, @Wk

I get the error

Msg 4924, Level 16, State 1, Line 4
ALTER TABLE DROP COLUMN failed because column 'WK @WK Sold' does not exist in table #Temp

I know that @param and @Wk work because I ran

EXEC sp_executesql N'select @Wk', @params, @Wk

and it worked. I know I can just run

EXEC ('ALTER TABLE #Temp DROP COLUMN [WK '   @Wk   ' Sold]')

but I'd like to use sp_executesql.

Is it even possible the way I have tried?

Thank you

CodePudding user response：

The problem has nothing to do with the variable here. The problem is that you think that a variable/parameter when used in the context of an object results in that variable being injected into the statement. That doesn't happen. SELECT 1 AS [@a] return return a column aliased as literally @a not a column where the alias is the value of a variable called @a.

What you need to do here is safely inject the value into the dynamic statement and ideally validate the name too:

DECLARE @Cntr SomeDataType; --I don't know what the datatype should be.

DECLARE @wk nvarchar(5),
        @Column sysname;

SET @wk = @Cntr; --No need for the cast here
SET @Column = (SELECT c.name
               FROM tempdb.sys.tables t
                    JOIN tempdb.sys.columns c ON t.object_id = c.object_id
               WHERE t.[name] LIKE N'#temp%'
                 AND c.name = N'WK 
    '   @wk   N' Sold');--Does your column name really have a carriage return and line break in it?

DECLARE @SQL nvarchar(MAX) = N'ALTER TABLE #Temp DROP COLUMN '   QUOTENAME(@Column)   N';';
EXEC sys.sp_executesql @SQL;

CodePudding user response：

Thank you for your responses. I'm all for validation, but Lamu's answer doesn't really do more that exec (@SQL) - which will throw an error. It appears that what I was looking for isn't possible.