Conditional IN clause in SQL Server

Question

I have got a stored procedure which has five parameters. I want to include them in where clause as follows

If parameter is not null then include it in query as an IN clause

That is, parameter value can be like 'Test' or 'Test,Best' etc. I'm converting this comma seperated values into table using function in SQL.

I tried to use COALESCE(@test,test_column) = test_column but i'm unable to include IN clause here (What if @test = 'Test,Best').

So, i want to do something like mentioned below

DECLARE @param varchar(max) = 'Test,Best';

Select * from table where CASE when @param is not null then table.column in (@param)

Any suggestions please.

Dear @J.Doe, Thomas correctly pointed you to a question, that shows you what you should do to achieve your goal. Don't be angry, please! Simply your question has been asked so many times here, that the most logical response is to point you to some of the existing questions. If you take a look at it, you will see that you could simply define a table type and use it in your `in` clause. To exclude it from the filtering, you should check are there any rows in it. — Andrey Nikolov, Dec 17 '18 at 10:58
@J.Doe this question has been asked and answered a lot of times. You are asking it the wrong way though - before you can filter by a list of values in `IN` or `JOIN` you need to pass a list of values to the query. A comma-separated list is just one way, and not the best either - it has size limitations, takes more space, has no statistics. TVPs are better but they still have no statistics. ORM-generated queries are the best option because values in an `IN` clause do have statistics — Panagiotis Kanavos, Dec 17 '18 at 11:31
@J.Doe all the answers you're going to get here are the same answers given multiple times in the duplicates. As for `CASE when @param is not null then` that's a bad practice called the [catch-all parameter](https://sqlstudies.com/2017/02/27/queries-with-optional-parameters/) which results in bad execution plans - the optimizer will use whatever value is passed the *first* time to create and cache an execution plan. You'll also find many questions and answers about this. — Panagiotis Kanavos, Dec 17 '18 at 11:35

score 1 · Answer 1 · edited Dec 17 '18 at 14:08

I've adapted this answer to your requirements.

First create User-Defined Table type:

CREATE TYPE dbo.ParamsList
AS TABLE
(
  Param varchar(50)
);

Then just use this parameter and other parameters in your stored procedures:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.ParamsList READONLY
 , @param1              INT             = NULL
 , @param2              NVARCHAR(150)   = NULL
 , @param3              NVARCHAR(150)   = NULL
 , @param4              NVARCHAR(150)   = NULL


AS
BEGIN
  SET NOCOUNT ON;

  SELECT 
  * 
  FROM YourTable
  WHERE YourColumn NOT IN (SELECT Param FROM @List)
END
GO

Conditional IN clause in SQL Server

1 Answers1