Execute @SQL That contains 'Select @Variable = Field1 from Table'


Hello

I'm trying to execute some dynamic sql however I'm running into a problem with my variables .

code sample

set @SQL = 'Select @Variable = Field1 from ' + @DBNAME + '.dbo.Table'
exec @SQL

error Msg 137 ... Must declare the variable @Variable.

Since the @variable is to be used to store the returned field value I can't simply do  'Select ' + @Variable + ' = Field1 from ' + @DBNAME + '.dbo.Table' or else it evaluates the @variable to nothing and the query ends up being ...  'Select  = Field1 from DbName.dbo.Table' which of course fails.

How can I format this to work.

DarebearAsked:
Who is Participating?
 
wnrossCommented:
Sorry, not that I'm aware of, since the variables are scoped by the execute statement itself:

exec sp_executesql @SQL

is actually

exec (sp_executesql @SQL)

So no outer variables can be affected by an execute statement.  
Temptables are your best option here:
create table ##c (
       temp varchar(100)
)
set @sql = 'insert into ##c Select Field1 from ' + @DBNAME + '.dbo.Table'
exec (@sql)
select @Variable = temp from ##c

-- Display Results
select @Variable

drop table ##c

Cheers
-Bill

0
 
wnrossCommented:
DECLARE @Variable varchar(120)
set @SQL = 'Select @Variable = Field1 from ' + @DBNAME + '.dbo.Table'
exec @SQL
GO

Try that.

Cheers,
-Bill
0
 
DarebearAuthor Commented:
Bill

Sorry the variable is actually declared further up in my code not shown..I gues my example is a bit misleading. Your code sample will fail.  Apparently you can't use exec with dynamic sql that returns data into a variable. I'm looking for another way to do it with out using a temp table. I have about 50 of these statements in my code. I'd be curious to know if there are any sql 2005 workarounds?
0
 
wnrossCommented:
Thanks for the points, did that get you past your hurdle?

-Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.