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


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.

Who is Participating?
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


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

Try that.

DarebearAuthor Commented:

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?
Thanks for the points, did that get you past your hurdle?

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.