storing the result of a dynamic select statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: storing the result of a dynamic select statement

  1. #1
    Gareth King Guest

    storing the result of a dynamic select statement


    I am trying to store the result of a dynamic sql query in a variable

    eg.
    /*--------------------------------------------------*/
    Create Procedure Test
    @Column nvarchar(50) = null

    AS
    Declare @ColValue sql_variant

    Set @ColValue = exec('Select top 1 ' + @Column + ' from Table1')
    print @ColValue

    /* Do stuff with @ColValue */
    /*--------------------------------------------------*/

    Unfortunately the exec statement doesn't return the value to @ColValue.

    Any ideas would be appreciated.
    Thanks
    Gareth
    Share on Google+

  2. #2
    Jim Marr Guest

    Re: storing the result of a dynamic select statement


    If you are using SQL 2K try using the table data type instead of varchar.
    Search BOL.


    "Gareth King" <kingslug@hotmail.com> wrote:
    >
    >I am trying to store the result of a dynamic sql query in a variable
    >
    >eg.
    >/*--------------------------------------------------*/
    >Create Procedure Test
    > @Column nvarchar(50) = null
    >
    >AS
    > Declare @ColValue sql_variant
    >
    > Set @ColValue = exec('Select top 1 ' + @Column + ' from Table1')
    > print @ColValue
    >
    > /* Do stuff with @ColValue */
    >/*--------------------------------------------------*/
    >
    >Unfortunately the exec statement doesn't return the value to @ColValue.
    >
    >Any ideas would be appreciated.
    >Thanks
    >Gareth


    Share on Google+

  3. #3
    Rob Vieira Guest

    Re: storing the result of a dynamic select statement

    Anything done with the EXEC statement (dynamic SQL) runs in it's own scope -
    that means that you can't get to the variables inside of the EXEC and it
    also can't get at the variables outside the EXEC other than for the string
    construction of what exactly the EXEC is supposed to do.

    Since a temp table is scoped to the connection, the typical solution to this
    problem is to create a temp table, save the value to that temp table, then
    retrieve it from the temp table. Tedious and troublesome (for example, what
    if your datatype varies?), but about all there is.


    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com

    "Gareth King" <kingslug@hotmail.com> wrote in message
    news:3a6ea904$1@news.devx.com...
    >
    > I am trying to store the result of a dynamic sql query in a variable
    >
    > eg.
    > /*--------------------------------------------------*/
    > Create Procedure Test
    > @Column nvarchar(50) = null
    >
    > AS
    > Declare @ColValue sql_variant
    >
    > Set @ColValue = exec('Select top 1 ' + @Column + ' from Table1')
    > print @ColValue
    >
    > /* Do stuff with @ColValue */
    > /*--------------------------------------------------*/
    >
    > Unfortunately the exec statement doesn't return the value to @ColValue.
    >
    > Any ideas would be appreciated.
    > Thanks
    > Gareth



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center