setting output of a dynamic sql query to a variable

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 3, 2005 at 9:22 am

  #169348

  Hi,

  I am trying to get the result of a dynamic sql query into a variable but I am not knowing how to do it.

  declare @queryString VARCHAR(1000)

  set @queryString = 'SELECT MAX(PERIOD) FROM dbo.Table1'

  EXEC (@queryString)

  DECLARE @result DATETIME

  Now I would like to set the result of the execute statement to the variable @result. how could I do that?

  Thanks,

  Sridhar.

• deepforest

  SSCommitted

  Points: 1780

  More actions

  November 3, 2005 at 9:42 am

  #602176

  You can try something like:

  declare @queryString varchar(1000),

             @result int

  set @queryString='create table r (result int) insert into r SELECT MAX(PERIOD) FROM dbo.Table1'

  exec (@queryString)

  select @result = result from r

  drop table r

  select  @result

   

  peter

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 3, 2005 at 9:46 am

  #602179

  Thanks peter.

• Adrienne250

  Right there with Babe

  Points: 737

  More actions

  November 3, 2005 at 9:50 am

  #602181

  I learned that you can use the sp_executeSql a couple of months ago:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=199866#bm199953

  In the last post from Ray M (Thanks Ray!)

  Example:

  Use Northwind

  Declare @result int

  declare @queryString nvarchar(1000)

  Set @queryString = 'Select @result = max(employeeID) from orders'

  exec sp_executeSql @queryString, N'@result int OUTPUT', @result = @result OUTPUT

  print @result