Execute procedure in statement in NOT IN()

I have the following stored procedure in SQL Server

CREATE PROCEDURE storedProcedureExample
    (@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 storedProcedureExample @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. I get an error

Incorrect syntax near ‘)’

on the closing parameters of the EXEC.

I’m using SQL Server Management Studio v17.1

Answer

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.

Advertisement

Answer