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.

Here is the link to the article Tom H. wants you to read. sommarskog.se/dynamic_sql.html If you use dynamic sql at all, this is a must read article. — HLGEM
– HLGEM, Commented Feb 11, 2009 at 14:54

Tim Lentine · Accepted Answer · 2009-02-11 14:10:50Z

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

Tom H · Accepted Answer · 2009-02-11 13:42:13Z

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.

Cade Roux · Accepted Answer · 2009-02-11 14:37:59Z

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

booyaa · Accepted Answer · 2011-05-18 09:14:57Z

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

Collectives™ on Stack Overflow

Dynamic sql statement to update a variable

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related