Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6

    Unanswered: query the names of stored procedure results columns

    So I am a little miffed about the diligence of the unit and QA testing of database changes lately because I deal with a lot of the production issues introduced by my team mates.

    I have a little bit free time today at work, and I thought I might build a little engine that tests SPs that return result sets of stored procedures for my developers and does a perfomance number comparison. I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.

    Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...

    INSERT INTO #datacompare(col1,col2)
    EXEC mystoredprocedure

    ... but I can not seem to figure out how to dynamically gather the schema of the result set. This is the level if testing that I apply myself to things that I am concerned about. I was just hoping to give a more push button tool to my developers.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 11
    Unfortunately, the structure of the resultset is not stored anywhere, because different paths through the code can result in different queries being run.

    create proc crazyivan
    if (datepart(mi, getdate()) between 15 and 45)
        select 'starboard'
        select getdate(), 'which way do you want to go?'
    select 'right

Posting Permissions

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