I have a situation in which I have to programmatically generate a global temporary table name. Into the global temporary table with the programmatically generated name, an agregate value (SUM()) is then stored using sp_executesql. I then have to extract that value from the global temp table into an @Var for use in calculations. The usual syntax I would use is:

SELECT @Var = cnt from ##GlobalTempTable

However, this syntax will not work since I don't know the tamp table name at design time and the above syntax will not deposit the data into a local variable when run using sp_executesql.

If I could get the value directly it would be better but it's the same problem with the above syntax when trying to get the data into a local variable. I would prefer to do something like:

SET @Stmt = 'SELECT @Var = SUM([' + LTRIM( RTRIM( @Speaker ) ) + ']) FROM N=MyTable WHERE [ResponseValue] <> 9999'
exec sp_executesql @Stmt

But this does not deposit the value in a local @Var and the column name (@Speaker) is not known until run time necessitating the use of sp_executesql.

Is there a way to get around this and get the value of the agregate SUM() into a local variable when the column name is not known at design time?

Thanks,
Bill