SQL Server - Call stored procedure in a IN Statement [duplicate]

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
– marc_s, Commented Mar 27, 2014 at 9:19

Madhivanan · Accepted Answer · 2014-03-27 09:20:58Z

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)

jwize · Accepted Answer · 2014-03-27 09:22:38Z

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

I simplified the query, but actually the new_stored_procedure should do an insert, which function can't do.

Collectives™ on Stack Overflow

SQL Server - Call stored procedure in a IN Statement [duplicate]

2 Answers 2

Comments

1 Comment

Hot Network Questions