Dynamic sql statement to update a variable

Question

Hi, 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

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.

Answer 2

This worked for me.

I declared a temp table and used it to receive the values from the select statement.

Something like below.

declare @i int

declare @v int create table #t (val int) insert into #t exec ('declare @i int set @i = 0 select @i+1') select * from #t

Answer 3

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 4

We've used sp_executesql. Here's another example of a parameterized record count:

DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT @RecordCount = COUNT(*) FROM [{@SchemaName}].[{@TableName}]'
SET @sql = REPLACE(@sql, '{@SchemaName}', @SchemaName)
SET @sql = REPLACE(@sql, '{@TableName}', @TableName)

DECLARE @RecordCount AS int
EXEC sp_executesql 
    @query = @sql, 
    @params = N'@RecordCount INT OUTPUT', 
    @RecordCount = @RecordCount OUTPUT

Answer 5

Here is the link to the article Tom H. wants you to read.

http://www.sommarskog.se/dynamic_sql.html

If you use dynamic sql at all, this is a must read article.