Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: Exec myStoredProc into #tmp

    I need to write SQL code to regression test an existing stored procedure (which I can't change).
    The SP returns a resultset with named columns, like this:-

    Code:
    exec dbo.myStoredProc
    fieldA fieldB
    ------ ------
    0 0
    1 2

    In my regression test I'd like to capture those results in a table, BUT I need to preserve the fieldnames returned by the stored proc, because I need to make sure they haven't changed.
    In other words, I'd like to do:-

    Code:
    select *
    into #tmp
    from (
    	exec dbo.myStoredProc
    ) g
    Then I can check that the column names in #tmp are correct.
    But this syntax isn't legal. So how can I do this?

  2. #2
    Join Date
    Jun 2009
    Posts
    2
    insert #tmp
    exec dbo.sprocname param, param

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Thanks, but that doesn't help. I just get:-

    Msg 208, Level 16, State 0, Line 8
    Invalid object name '#tmp'.

    And if create #tmp beforehand then I have to name the columns, so even if the column names returned by the SP have changed the code will still work.

Posting Permissions

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