Dynamic query: EXEC - Need to get a return value from query

I use dynamic SELECT statement trying to achive the followng:

DECLARE @.table varchar(50)

DECLARE @.ColumnA varchar(50)

DECLARE @.Result float

SET @.table = 'TABLE_A'

SET @.ColumnA = 'COLUMN_1'

SET @.SQL = ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

EXEC (@.SQL)

I need to assign the outcome of this statement to the @.Result variable. It does not work because of different contexts for @.Result. It would run if I do

SET @.SQL = 'DECLARE @.Result float '

SET @.SQL = @.SQL + ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

But in this case @.Result is not 'visible' outside EXEC statement.

One way to solve this is to use INSERT statement into temp table and then read result from the temp table.

Are there any more elegant solutions?

Thanks!

Sorry, but the code in EXEC() is considered to be its own batch, so you will need to persist it in a table, and temp tables are usually used for this. BTW, what did you need to do with the @.Result value? You may be able to put that in the same EXEC() code.

Thanks, Dean

|||

There are different ways to execute dynamic sql. The one that you need is sp_execsql. It allows you to declare parameters -- even output parameters. A key to remember is that an output parameter must be declared as output twice, in much the same way that an output parameter is declared both inside a stored procedure and on the exec line.

Your code should look like:

Declare @.result float

SET @.SQL = @.SQL + ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

exec sp_execsql @.sql, N' @.Result float Output', @.Result output

The command wants NVarchar() parameters. Make sure @.sql is declared NVarchar() and include the N on the literal declaring the parameter. You can have multiple parameters if needed.