Dynamic sql statement to update a variable

Question

my sql statement is something like this below

DECLARE @OLD_NAV_VALUE AS INT
DECLARE @FINAL AS INT

SELECT @OLD_NAV_VALUE = [col1] from TBL_BA where DATE = @id_Date

SET @FINAL =  @OLD_NAV_VALUE * 50

But the problem i am haveing here is that the column name in the select statement which is given as [col1] is a dynamic value. So i am trying something like this below.

DECLARE @OLD_NAV_VALUE AS INT
DECLARE @FINAL AS INT

EXEC('SELECT @OLD_NAV_VALUE = [' + @DYNAMIC_COL_NAME + '] from TBL_BA where DATE = ' + @id_Date)

SET @FINAL =  @OLD_NAV_VALUE * 50

this gives an error that @OLD_NAV_VALUE has to be declared. So i tried declaring @OLD_NAV_VALUE inside the EXEC statement. But if i do this i am not able to use the same outside the EXEC statement.

Please let me know how to do this.

Answer 1

You can also use the sp_executesql statement with an output parameter:

declare @field nvarchar(50);
set @field = N'FieldToSelect';

declare @sql nvarchar(3000);
declare @parmDefinition nvarchar(500);

SET @parmDefinition = N'@returnValueOUT nvarchar(50) OUTPUT';

set @sql = N'SELECT @ReturnValueOUT = ' + @Field + ' FROM [TableName] WHERE [SomeCondition]'

declare @returnValue nvarchar(50);
EXECUTE sp_executesql @sql, @parmDefinition, @returnValueOut = @returnValue OUTPUT;

SELECT @returnValue

Answer 2

First, I'd suggest that you do a Google on "Erland dynamic SQL" and read his white paper on the subject.

Your design is probably not the best if it requires that you use a dynamic column name like this.

The reason that you can't do what you're trying to do is that everything in the EXEC is entirely in its own scope. If you absolutely have to do it this way though then you could use a table (either a normal table, or a global temporary table) to store the value for use outside of the EXEC.