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.

EXCEPTION_ACCESS_VIOLATION

I got this error when i try to insert a data in a table
with a Text Column in a distributed application (MSTDC
COM+ ASP), somebody have some one idea about this
error ? more descriptions from event viewer :
Error : 0 Severity : 19 State : 0
SqlDumpExceptionHandler: Process 61 generated fatal
exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.Check your SQL Server error log for the exact stack dump that goes with the
Access Violation (AV) message. Pull out the keywords from the first couple
of lines of the dump and search for them in the online Knowledge Base.
Also, use profiler to find out what the exact command is that results in
the AV and see if the same thing happens when you run that command from
Query Analyzer.
Are you on the latest service pack for SQL Server?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.