How can we store results of Dynamic Queries in variables?

Question

How can we store results of Dynamic Queries in variables?

Usman Tanveer

SSC Journeyman

Points: 94
More actions
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

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 2

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)

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 3

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