sql server except on stored procedure results

Question

I want to do this but with stored procedure results:

select * from table where column=whatever
except
select * from table2 where column=whatever

so

exec sp1 args
except
exec sp2 args

my sps don't have return values they just take arguments and return the results of select statements

Answer 1

Give this a go.

CREATE PROCEDURE usp_sp1_Except_sp2
@sp1 args     --<-- All the params sp1 Expects
@sp2 args     --<-- All the params sp2 Expects
AS
BEGIN
  SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#SP1_Results') IS NOT NULL
DROP TABLE #SP1_Results

IF OBJECT_ID('tempdb..#SP2_Results') IS NOT NULL
DROP TABLE #SP2_Results


CREATE TABLE #SP1_Results
(
  -- Define table structure here
)

CREATE TABLE #SP2_Results
(
  -- Define table structure here
)


INSERT INTO #SP1_Results
EXECUTE dbo.sp1 @sp1

INSERT INTO #SP2_Results
EXECUTE dbo.sp2 @sp2

SELECT * FROM #SP1_Results
EXCEPT 
SELECT * FROM #SP2_Results

  SET NOCOUNT OFF;

END