exec an SP to return rows to another SP

Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??

Something like...

CREATE procedure spThisSP(@.param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @.param)

CheersYou could re-write the sub proc as a function|||Thanks for the reply pkr but I was hoping that I could re-use stored procedures somehow?
Duplicating the code one way or another seems "wrong"...

Cheers|||You could use a temp table...
select into #temp
exec proc1

select * from #temp

But I HATE temp tables.|||i didnt understand what you are trying to do but i know you can call a stored proc from another stored proc..

hth|||pkr,

But I HATE temp tables.

May I ask why? I'm not being critical, just trying to understand various people's like and dislike of temp tables.

Thanks,
Don|||My main hate is a really irrating bug/feature I've hit in the past where the db is convinced the temp table already exists when you try to create it but says it doesn't exist when you try to use it!!

Besides that, there are too many locking issues with tempDb that make it very difficult to effectively police developer code - IMO. I just try to avoid them wherever possible.|||Cool, thanks.

Don|||Thanks for all the replies. From your discussion I found thisarticle which provides some more helpful pointers in using temp tables.