SQL Server - Call stored procedure in a IN Statement

Question

First of all, is it even possible ?

I have a stored procedure which looks like:

SELECT this FROM table WHERE this IN (SELECT that FROM another_table WHERE that = @Param)

I would like to replace (SELECT that FROM another_table WHERE that = @Param) by another stored procedure

I am having trouble finding the right syntax to make it work. I tried:

SELECT this FROM table WHERE this IN (EXEC new_stored_procedure @Param)

But this doesn't work. Does somebody know the right syntax to do so ?

Thank you for helping

No, this is not possible. You'd have to execute the stored procedure first, and store the results into a temporary table (or table variable) and then use a `SELECT .. FROM ...` in the `IN` clause — marc_s, Mar 27 '14 at 09:19

score 1 · Accepted Answer · answered Mar 27 '14 at 09:20

You can create a temporary table

-- match exact columns with datatype returned from the stored procedure
create table #temp(col1 int, col2 .... ) 

insert into #temp(col1,...)
EXEC new_stored_procedure @Param


SELECT this FROM table WHERE this IN (select col from #temp)

score 1 · Answer 2 · answered Mar 27 '14 at 09:22

1

You can use a Table-Valued Function

CREATE FUNCTION [dbo].[Get10Companies]
(   
 @DepartmentId Int
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT TOP (10) ID from company
            WHERE DepartmentId = @DepartmentId
)


 SELECT * from ( Select * from Get10Companies (1104)) t

answered Mar 27 '14 at 09:22

jwize

4,230
1
33
51

I simplified the query, but actually the new_stored_procedure should do an insert, which function can't do. – Pierre Roudaut Mar 27 '14 at 09:34

SQL Server - Call stored procedure in a IN Statement

2 Answers2