Subject Re: Comparing two Sps
Author Adam
--- In, "Alan McDonald" <alan@m...>
> > Hello,
> >
> > I need to write a quite specific utility where I need to compare 2 sps
> > belonging to 2 databases and need you advice and ideas.
> >
> > I have considered two possible options :
> >
> > 1) grab the stored procedure text, replace all tab, multiple spaces
> > and line feeds by a single space for both SP source code and then do a
> > simple compare
> >
> > 2) grab the BLR representation and compare both.
> >
> > I assume that the BLR would be the same regardless of spaces, tab,
> > extra lines and that it would be probably be the safest comparison
> >
> > Any comments or opinions will be very welcomed
> >
> > Didier
> there are db compare utilities which use a reference and target db, they
> compare and result in an alter statement for the procedure.
> Is this no good for your purpose?
> Alan

I agree with Alan. It is likely that if the "same" SP exists in both
databases (ie. The same version of the SP), there would be no
difference in white space and comments etc.

I am not familiar with what information BLR stores and what it doesn't
store, and whether differences in white spaces and comments create a
different BLR (I doubt it), but it just seems like overkill when all
you are truly after is whether their definitions are bytewise
identical, or whether they are different. You could then use one of
the many database comparison tools to tell you and write a change
script for you.

To test whether functionwise they are identical, you should use the
xUnit derivative in whatever language you are most familiar, and your
test cases will prove it 5 seconds whether they are identical or not.
Of course this assumes you have appropriate coverage in your test
cases, which may not be a reality. If you do not have appropriate test
cases, firstly use your DB comparison tool to tell you whether they
are the same, then start writing some test cases.