Ad Hoc query assigns results to local variables

Question

Hi is it possible for me to do this somehow? When i run the statement i get an exception, @Price_Plan is not delared, so obviously the adhoc query does not have scope to access @Price_Plan. Is there a workaround, or a better way to query a table whose name changes per execution of this query.

DECLARE @Price_Plan varchar(3), @MNP_Network varchar(3), @GSM_Code varchar(3), @GEO_Dist varchar(6),
     @Call_ProdNo varchar(7), @Call_Time datetime, @CallId int, @dtl_call_dur int,
     @Volume varchar(10), @Call_Cost int

--Assume CallId has a value

SET @Sql =
    'SELECT
     @Price_Plan = Price_Plan, @MNP_Network = MNP_Network, @GSM_Code = GSM_Code, @GEO_Dist = GEO_Dist,
     @Call_ProdNo = Call_ProdNo, @Call_Time = Call_Time, @dtl_call_dur = dtl_call_dur,
     @Volume = Volume
     FROM ' + @TableName + ' 
     WHERE CallId = ' + CONVERT(varchar(10),@CallId) + ''
    PRINT  @SQL
    EXEC (@Sql)

Answer 1

Look into sp_executesql

Answer 2

Are you sure this is this error? I tested that query and it returned two errors:

- Not declared @TableName
- Not declared @Sql

When you declare those variables it should work good.

But better way is to use sp_executesql.

An example:

EXEC sp_executesql 
      N'SELECT * FROM AdventureWorks.HumanResources.Employee 
      WHERE ManagerID = @level',
      N'@level tinyint',
      @level = 109;

First argument is the query with parametes, 2nd argument - parameters names with types separated by commas, and then goes the actual values of the parameters.

EDITED:

Here is the another example using OUTPUT parameter:

DECLARE @retCnt INT

EXEC sp_executesql 
    N'SELECT @retCnt = COUNT(*) FROM sys.tables',
    N'@retCnt INT OUTPUT',
    @retCnt = @retCnt OUTPUT

SELECT @retCnt

SELECT return 5 on my computer.