Post reply

How can we store results of Dynamic Queries in variables?

  • February 16, 2005 at 10:22 pm

    #87717

    Can some one tell me that how can i get the multiple or single value results of dynamic query into a local variables.

    e.g in following codes i want to store the values of fname into local variables

     

    CODE 1:

    Create Procedure Getnames @tableName VARCHAR(100)

    AS BEGIN DECLARE @Query VARCHAR(5000)

    Set @Query = 'SELECT fname FROM ' + @tableName

    Execute @Query

    END

     

    CODE 2:

    Create Procedure GetName @tableName VARCHAR(100), @id INT

    AS BEGIN DECLARE @Query VARCHAR(5000)

    SET @Query = 'SELECT fname FROM ' + @tableName + ' WHERE id = ' + @id

    Execute @Query

    END

     

  • February 17, 2005 at 1:50 am

    #542724

    See if this helps: http://www.sommarskog.se/dynamic_sql.html

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 18, 2005 at 1:46 am

    #543021

    Use sp_executesql instead on EXEC. Then you can use output variables or a table variable to store the resultset.

    HABIB.

     

     

    Kindest Regards,

    Habib Zmerli (MVP)

  • February 18, 2005 at 1:58 am

    #543023

    To get output information from dynamic SQL queries, use sp_executesql instead of EXEC to actually execute your queries.  The technique is to write your output to a variable which is used as an output parameter by sp_executesql. 

    Note, however, this that works for one or more scalar values, but not for recordsets.  In otherwords, your first example returns all rows from a table, and this method will not work.  However, your second example looks like it returns a single row, so the method works.

    The only real trick is that sp_executesql likes nvarchar, not varchar for the sql string.  Look up sp_executesql in Books Online for more information.

    CREATE PROCEDURE GetName
  (@TableName  varchar(100)
  ,@ID         int
  )
AS
DECLARE @Query nvarchar(500)  -- string must be unicode
       ,@fname  varchar(50)   -- variable to hold one first name
    

-- note that the query string includes the variable @id within

-- note also that the @FirstName var is just a placeholder and 
--    can have a different name than the external output varaible


SET @Query = 'SELECT @FirstName = fname FROM ' 
           + @tableName 
           + ' WHERE id = @id'


    exec sp_executesql @Query
                  , N' @id int, @FirstName varchar(50) OUTPUT'
                  , @id, @fname output
    print @fname
     

    Hope this helps,

    Scott Thornburg

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply