Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: Capturing exec or sp_executesql result

    Hi.. perhaps it's stupid, but i am really a newbie..
    How can i capture the result generated from exec('select ... ') or sp_executesql? such as

    sp_executesql 'SELECT max(ID) FROM Yr' + year(getdate) + '.dbo.Sales' + month(getdate)

    How can i get the 'max(ID)' data? I must get the data from sp_executesql or execute form.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    as far as I know the only way to capture the results from EXECUTE is to insert it into a temp table and then select out what you are looking for.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can set a variable from sp_executesql

    declare @id int, @sql nvarchar(1000)
    select @sql = 'SELECT max(ID) FROM Yr' + year(getdate) + '.dbo.Sales' + month(getdate)

    exec sp_executesql @sql, N'@id int output', @id output

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    oops

    declare @id int, @sql nvarchar(1000)
    select @sql = 'SELECT @id = max(ID) FROM Yr' + year(getdate) + '.dbo.Sales' + month(getdate)

    exec sp_executesql @sql, N'@id int output', @id output


    see
    www.nigelrivett.com
    sp_executesql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •