comparing the results of two stored procedures.

I wanted to verify the output of two stored procedures I hoped I could
use EXCEPT. But that doesn't work.
I tried
execute usp_myProc @.param=1, @.param=2
except
execute usp_myProc1 @.param=1, @.param=2
so I could see if the results were different. Then I tried:
select * from ( usp_myProc @.param=1, @.param=2) as a
figuring I could then do an into #compareTable one and #compareTable2
and do an except on those. However it doesn't like that either. I
thought a derived table works on the assumption that a from clause
expects a table, so as long as you return it a table it will accept
that as a valid parameter. But this doesn't work. Any SQL genius out
there devised a way to compare the results of two stored procedures
easily?Create temporary tables that match the output format of the two stored
procedures, then populate those tables with the output:
INSERT INTO #output (EXEC usp_myProc @.param1 = 1, @.param2 = 2)
INSERT INTO #output1 (EXEC usp_myProc1 @.param1 = 1, @.param2 = 2)
Now you can compare the ouputs of the procedures to get the difference:
SELECT t1.col1, t1.col2 --more columns
FROM #output t1
WHERE NOT EXISTS
(
SELECT 1 FROM #output1 t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2 --and so on for more columns
)
"bryanmurtha@.yahoo.com" wrote:

> I wanted to verify the output of two stored procedures I hoped I could
> use EXCEPT. But that doesn't work.
> I tried
> execute usp_myProc @.param=1, @.param=2
> except
> execute usp_myProc1 @.param=1, @.param=2
> so I could see if the results were different. Then I tried:
> select * from ( usp_myProc @.param=1, @.param=2) as a
> figuring I could then do an into #compareTable one and #compareTable2
> and do an except on those. However it doesn't like that either. I
> thought a derived table works on the assumption that a from clause
> expects a table, so as long as you return it a table it will accept
> that as a valid parameter. But this doesn't work. Any SQL genius out
> there devised a way to compare the results of two stored procedures
> easily?
>|||You can insert into a table from a stored procedure with the same column
layout. Once done, you can query the 2 tables for differences.
create table #myProcA (x int, a int, b int, c int)
insert #myProcA exec spProcA
create table #myProcB (x int, a int, b int, c int)
insert #myProcB exec spProcB
select
*
from
#myProcA as A
left join #myProcB as B
on B.x = A.x
where
A.a <> B.a or
A.b <> B.b or
A.c <> B.c
<bryanmurtha@.yahoo.com> wrote in message
news:1140545979.907341.185830@.o13g2000cwo.googlegroups.com...
>I wanted to verify the output of two stored procedures I hoped I could
> use EXCEPT. But that doesn't work.
> I tried
> execute usp_myProc @.param=1, @.param=2
> except
> execute usp_myProc1 @.param=1, @.param=2
> so I could see if the results were different. Then I tried:
> select * from ( usp_myProc @.param=1, @.param=2) as a
> figuring I could then do an into #compareTable one and #compareTable2
> and do an except on those. However it doesn't like that either. I
> thought a derived table works on the assumption that a from clause
> expects a table, so as long as you return it a table it will accept
> that as a valid parameter. But this doesn't work. Any SQL genius out
> there devised a way to compare the results of two stored procedures
> easily?
>|||Thanks both, I have 37 stored procs which I had to upgrade from SQL-89
syntax to SQL-92 and write test cases to verify the output is the same.
I knew you could do it if you defined the temp tables but I don't think
there is any way around that. I tried OpenRowset and all that put the
queries can't have parameters. I didn't want to have to define 37
tables x 2 but I don't think there is anyway around that. But I
appreciate the two of you taking the time to respond.
Regards,
Bryan