Ragmar Ragmar - 28 days ago 8
SQL Question

Execute procedure in statement in NOT IN()

Having the following store procedure in SQL Server

CREATE PROCEDURE storeProcedureExample
(
@displayName NVARCHAR(256)
)
AS

SELECT users.id --user.id is INT
FROM users
WHERE users.displayName = @displayName
--there could be many ids from the same display name


How can I call it in a statement NOT IN?

Error Example:

DECLARE @displayName NVARCHAR(256) = 'SomeRandomName'

SELECT *
FROM users
WHERE users.id NOT IN (EXEC storeProcedureExample @displayName)


Note: I'm not interested in rewriting it more efficient. I just want to know how can I call the procedure within the NOT IN parameters. It is giving me "Incorrect syntax near ')'" on the closing parameters or in the
EXEC


I'm using SQL Server Management Studio v17.1

Answer Source

You can't directly. This is why you should use a table valued function rather than a stored procedure.

With a stored procedure, you would need to create a local table, exec() into the table, and then use the temporary table for the query.