Post reply

How to Comapre the data returned by two Storedprocedures

  • January 25, 2008 at 5:06 am

    #186002

    Hi,

    Sub: How to Comapre the data returned by two Storedprocedures

    I have a stored procedure which Connects to returns the data retrieved by a select query.So when I execute that stored procedure the result will get displayed on the management studio in a Table format.

    Question:- Now I want to execute this on two different Databases (Two Databases will contain the same tables,Same data ).And I want compare the result returned from those two different Databases.

    Is there any good way to achieve this problem.suggest If there are any scripts/tools by which we can compare the result of two stored procedures.

    Thank you inadvance.

  • January 25, 2008 at 5:28 am

    #771676

    Create #temp(id int,colums...)

    insert into #temp(1 as id,....)

    exec server1.dbname.db.proc

    insert into #temp(2 as id.....)

    exec server2.dbname.db.proc

    you will have all the data in a table and you can then compare

  • January 25, 2008 at 8:07 am

    #771763

    I have Tried it.

    I have create a #Temp1 Table with the req column set (Same column set returned by the proc)

    INSERT #Temp1(col1,col2)

    EXEC Proc1

    Then I am getting the error as "An INSERT EXEC statement cannot be nested."

    Here Prc1 is calling another Proc2 which contains the INSERT EXEC ....Statement.

    Note: Here I am not allowed to do any code change in Proc1,Proc2

    Thank You In Advance.

  • January 25, 2008 at 8:37 am

    #771798

    Change your procs to insert the result data into a temporary table and then compare those two tables.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 25, 2008 at 8:39 am

    #771799

    Do you have to use the stored procedure or can you perform the select query? Are you looking for differences?

    You can use except to get the differences between the two tables. (this query will return everything in the first select that is not existant in the second select.)

    select *

    from db1.dbo.table

    except

    select *

    from db2.dbo.table


    My blog: http://jahaines.blogspot.com

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

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