Dynamic sql statement to update a variable

Question

my sql statement looks something like this:

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 is that the column name in the select expression specified as [col1] is a dynamic value. So I am trying to do 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 the error that @OLD_NAV_VALUE should declare. So I tried to declare @OLD_NAV_VALUE inside the EXEC statement. But if I do, I cannot use it outside the EXEC statement.

Please let me know how to do this.

Answer 1

It worked for me.

I declared a temporary table and used it to get 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 2

sp_executesql :

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 3

-, Google "Erland dynamic SQL" .

, , , , , .

, , , , EXEC . , ( , ) EXEC.

Answer 4

sp_executesql. :

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