Obtain output parameter values from dynamic SQL [closed]

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
– Santhosh Ramini, Commented Apr 3, 2013 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
– Aaron Bertrand, Commented Apr 3, 2013 at 11:49

Aaron Bertrand · Accepted Answer · 2013-04-03 11:50:46Z

29

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;

edited Apr 3, 2013 at 11:50

answered Apr 3, 2013 at 11:45

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Santhosh Ramini Over a year ago

In my requirement, "TABLE"'s Name is dynamic (say 'TABLE_2012','TABLE_2013') and so i should go for dynamic SQL

beneM Over a year ago

Could not have been explained better and more concisely. Thank you, Aaron!

Carl Michaels · Accepted Answer · 2013-04-03 11:54:38Z

4

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

answered Apr 3, 2013 at 11:54

Carl Michaels

1251 gold badge3 silver badges14 bronze badges

Comments

Sandip Bantawa · Accepted Answer · 2013-04-03 12:05:07Z

-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 3, 2013 at 12:05

answered Apr 3, 2013 at 11:45

Sandip Bantawa

2,9004 gold badges35 silver badges48 bronze badges

3 Comments

Aaron Bertrand Over a year ago

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.

Sandip Bantawa Over a year ago

@AaronBertrand I have modified my answer, isn't that okay??

Aaron Bertrand Over a year ago

Ok, now you've solved the problem, but it isn't very efficient to create a #temp table to hold a single value...

Collectives™ on Stack Overflow

Obtain output parameter values from dynamic SQL [closed]

3 Answers 3

2 Comments

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Linked

Related