Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    11

    Unanswered: compare Utilities for comparing stored procedure results?

    I have large stored procedures in SQL Server 2005 that often get updated. Sometimes it is very difficult to recognize how one change could impact the overall results. I would like to have some sample input that I could test during changes and see it compare the data results before & after my changes. This would help me quickly identify if the results are fine to pass through.

    So basically I would like an easy way to compare the results of 2 stored procedures. Any suggestions or utilites that would help me do this?

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    1. you could dump the result set of each into a temp table, then compare the temp tables with a join as long as there is a meaningful key to join on. could use a full outer join and call RAISERROR if either side of the join has a null.

    2. you could use bcp to export the results of each proc to a file, then compare the files with a diff tool.

    of course neither of these helps you with the side-effects (such as updating/deleting rows) that the proc may have. all it does is compare the the results returned, if any.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    1. you could dump the result set of each into a temp table, then compare the temp tables with a join as long as there is a meaningful key to join on. could use a full outer join and call RAISERROR if either side of the join has a null.
    Easier to use a union in a derived table and return all rows with a COUNT(*) = 1 based on a group by of that table. It makes it easier to isolate & compare attributes (as opposed to keys) and you don't have to figure out bodges to handle nulls.

    That, at any rate, is how I do it.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes, that's better for sure. good idea.

  5. #5
    Join Date
    Nov 2005
    Posts
    11
    Thanks for the tip. I was kind of hoping there is a simple utility out there that does this and allows me to compare other things automatically like the differences between the column headers. It seems I should just load this into .NET for a more dynamic comparison. The union idea sounds good.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cragi
    Thanks for the tip. I was kind of hoping there is a simple utility out there that does this and allows me to compare other things automatically like the differences between the column headers. It seems I should just load this into .NET for a more dynamic comparison. The union idea sounds good.
    There are. I don't use them however as they can be very inefficient. I think they do something like load the data into .NET\ C++ for dynamic comparisons Seriously - keep the stuff in SQL. You might be more comfy with .NET but it will be very inefficient compared to SQL.

    If you want a tool check out AdeptSQL Diff or ApexSQL Diff. I have no experience of any other comparison tools. I will warn you however - these are great for comparing schema definitions\ objects but inefficient when it comes to data. Jesse might have something up his sleeve too....

  7. #7
    Join Date
    Nov 2005
    Posts
    11
    thanks for the tips

Posting Permissions

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