setting output of a dynamic sql query to a variable

  • 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.

  • 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

  • Thanks peter.

  • 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

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

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