Using results from stored procedure in WHERE condition

Hi,

I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales".

So far I have created the inner proc

CREATE PROCEDURE spSales

@userID int

AS

SET NOCOUNT ON
SELECT SalesOppID
FROM Sales_Opportunities
WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped')
AND OppOwnerUserID = @userID

GO

This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as:

CREATE PROCEDURE spAssociate

(@userID int,
@containerType varchar(100),
@associateType varchar(100))

AS

SET NOCOUNT ON
SELECT AssociateID
FROM AppRelations
WHERE ContainerType=@containerType
AND ContainerID IN (EXECUTE spSales @userID)
AND AssociateType=@associateType

GO

I get an error "incorrect syntax near execute".

How can I use the results from the inner proc for the WHERE condition in my outer proc?

Any help is greatly appreciated. Thanks!

What I would suggest is doing Dynamic SQL here. As in let spSales return a OUTPUT variable or something that has all the ids in a comma seperated list, then in your spAssociate place that whole select statement in a varchar variable and append the return variable value you get when executing the spSales proc... something like below might work as an example.

DEFINE @vcTemp varchar(500)
DEFINE @vcReturnSet varchar(500) --Depending upon how many id's are returned

EXEC spSales @userID, @vcReturnSet
GO

SET @vcTemp = 'SELECT .... IN (' + @vcReturnSet + ')'

EXEC @vcTemp
GO

Now, this is just a rough draft but it should give a possible idea of how to over come this problem using Dynamic SQL.

===========
Brandon Russell
SQL Monkey

Stay away fro dynamic SQL. It is more efficient to join the tables directly.