Obtain output parameter values from dynamic SQL

Question

declare @var varchar(max),@course varchar(max)
set @var='ABC'
set @Query=''
set @Query='  
select @course=PARAM from TABLE where PARAM='''+@var+''''
print @Query
exec (@Query)

Since the above query returns an error as

Must declare the scalar variable "@course"

The query here is the alternative I am following right now to make that query successful.

declare @var varchar(max),@course varchar(max),@Query varchar(max)
Create table #temp(param1 varchar(max))
set @var='ABC'
set @Query=''
set @Query='insert #temp(param1)
select PARAM from TABLE where PARAM='''+@var+''''
print @Query
exec (@Query)
select @course=param1 from #temp
drop table #temp

Is there any other better alternative to this other than the solution I have mentioned above?

As you have seen, i want to assign the value retrieved from "TABLE" based on where condition. Here i used exec (@Query), because, in my requirement, "TABLE"'s Name is dynamic (say 'TABLE_2012','TABLE_2013') and so i should form the query dynamically. — Santhosh Ramini, Apr 03 '13 at 11:47
TABLE_2012 and TABLE_2013 sounds like a terrible design decision. Why not have a single table partitioned or clustered on a year column? Now you don't need dynamic SQL, the performance will be quite similar, and schema management will be a lot easier. — Aaron Bertrand, Apr 03 '13 at 11:49

Aaron Bertrand · Accepted Answer · 2013-04-03T11:50:46.897

Well I'm not sure that you actually need to use dynamic SQL here, but I suspect that you dumbed down a more complex example for us. (Tip: you don't need to do that.)

If this is all you're doing, then why not just:

SELECT @course = PARAM FROM dbo.Table WHERE PARAM = @var;

(Which doesn't even make sense - by definition @course and @var are either equal or the row doesn't exist.)

If your SQL is actually more complicated, then you need to STOP using EXEC() and embrace sp_executesql. One of the things this allows is much more flexibility with strongly-typed parameters (including OUTPUT). Here is a quick example:

DECLARE @table_name SYSNAME;

DECLARE @var VARCHAR(MAX), @course VARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @var = 'ABC', @table_name = N'TABLE_2012';

SET @sql = N'SELECT @course = PARAM FROM ' + @table_name 
  + ' WHERE PARAM = @var;'

EXEC sp_executesql @sql,
  N'@var VARCHAR(MAX),@course VARCHAR(MAX) OUTPUT',
  @var, @course OUTPUT;

PRINT @course;

In my requirement, "TABLE"'s Name is dynamic (say 'TABLE_2012','TABLE_2013') and so i should go for dynamic SQL — Santhosh Ramini, Apr 03 '13 at 11:53
Could not have been explained better and more concisely. Thank you, Aaron! — beneM, Mar 23 '23 at 09:27

score 4 · Answer 2 · answered Apr 03 '13 at 11:54

You can try this

declare @var varchar(max),@course varchar(max),@paramdef varchar(100)

set @var='ABC'
set @Query=''
set @Query=N'  
select @result=PARAM from TABLE where PARAM='''+@var+''''
set @paramdef=N'@result varhcar(20) OUTPUT'

execute sp_executesql @Query,@paramdef,@result=@course Output

SELECT @course

Sandip Bantawa · Answer 3 · 2013-04-03T12:05:07.217

-1

You can declare variable on the dynamic query itself

declare @var varchar(max)
set @var='ABC'
set @Query=''
set @Query='  
DECLARE @course varchar(max)
select @course=PARAM from TABLE where PARAM='''+@var+'''

SELECT @course
'
print @Query
exec (@Query)

--Reusing variable
    CREATE TABLE #temp
    (
        VALUE VARCHAR(MAX)
    )
    INSERT INTO #temp
    EXEC (@Query)
    SELECT * FROM #temp

edited Apr 03 '13 at 12:05

answered Apr 03 '13 at 11:45

Sandip Bantawa

2,822
4
31
47

And then how do you use the value of `@course` after the `EXEC` has finished? This eliminates the immediate error but it doesn't solve the actual problem. – Aaron Bertrand Apr 03 '13 at 11:52
@AaronBertrand I have modified my answer, isn't that okay?? – Sandip Bantawa Apr 03 '13 at 12:06
3

Ok, now you've solved the problem, but it isn't very efficient to create a #temp table to hold a single value... – Aaron Bertrand Apr 03 '13 at 12:07

Obtain output parameter values from dynamic SQL

3 Answers3

Linked

Related