sql-servert-sqlsplitwhere-clause

Passing Dynamic parameter using STRING_SPLIT to IN parameter

I am using SQL Server 2016 and I have to pass dynamic values to IN parameter.

For this reason, I am using a SPLIT_STRING() function that returns a table of items from a given comma-delimited string. It returns all the records if I passed the value like this (4,6,8) to the @Cylinders parameter.

WHERE CarCylinders IN (SELECT value FROM STRING_SPLIT(@Cylinders, ','))

But I have requirements where I want to pass Null value to the @Cylinder parameter. So that query returns all the records and ignores the condition.

I don't want to use dynamic SQL solution as the query is very complex and there are more than 20 input parameters.

Solution

  • Just use AND/OR logic:

    WHERE @Cylinders IS NULL
OR CarCylinders IN (SELECT value FROM STRING_SPLIT(@Cylinders, ','))