• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

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.

0
Darebear
Asked:
Darebear
  • 3
1 Solution
 
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:
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:
Thanks for the points, did that get you past your hurdle?

-Bill
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now