Dynamic SQL get one value from custom @Column into result variable

Question

I have been trying to accomplish this and nothing I searched on the topic so far worked. I have the following code inside a stored procedure:

DECLARE @sql NVARCHAR(500)
DECLARE @qty money
SET @sql = N'SELECT TOP 1 @qty = @QuantityColumnName FROM #TemporaryTable WHERE ID =  @Id'
EXEC sp_executesql @sql, N' @QuantityColumnName nvarchar(50),@Id bigint, @qty money OUTPUT', @QuantityColumnName = @QuantityColumnName, @Id = @Id, @qty = @qty OUTPUT;
//this does not execute

Where @Id is helping me loop through a #TemporaryTable and @QuantityColumnName is a parameter for my procedure of type nvarchar(50). The #TemporaryTable has 2 columns, let's say Quantity1 and Quantity2 and, based on some logic, I pass the column name to this procedure and use the value here:

UPDATE Stocks
SET Quantity = Quantity - @qty      
               WHERE blabla

And here

INSERT INTO Stocks(ArticleLotId, LocationId, Quantity, ReservedQuantity)
                VALUES (@artLotId, @locId, @qty, 0)

Error message '@qty' could not be evaluated.

I even tried another variant, also with no success, where I tried to store the result inside a table

--Extract and cast result from @QuantityColumnName
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT ' + @QuantityColumnName + ' FROM #TemporaryTable WHERE ID = @Id'
DECLARE @resultSql AS TABLE(col money)
INSERT INTO @resultSql EXECUTE sp_executesql @sql

and tried to use the result like this:

UPDATE Stocks
SET Quantity = Quantity - (SELECT col FROM @resultSQL)  
WHERE blabla

Could you please help me and explain what I'm doing wrong? Thank you in advance!

Answer 1

You can't parametrise an object name, you have to safely inject the value.

This should be what you're after:

DECLARE @QuantityColumnName sysname = N'ColumnName'; --Correct datatype for object names

DECLARE @sql NVARCHAR(MAX);        
DECLARE @qty money;
SET @sql = N'SELECT TOP 1 @qty = ' + QUOTENAME(@QuantityColumnName) + N' FROM #temporaryTable WHERE ID =  @Id;'
EXEC sp_executesql @sql, N' ,@Id bigint, @qty money OUTPUT', @Id = @Id, @qty = @qty OUTPUT;