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

Posted on 2006-04-26
Last Modified: 2011-09-20


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.

Question by:Darebear
    LVL 7

    Expert Comment

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

    Try that.


    Author Comment


    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?
    LVL 7

    Accepted Solution

    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


    LVL 7

    Expert Comment

    Thanks for the points, did that get you past your hurdle?


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    A short article about a problem I had getting the GPS LocationListener working.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now