Set where clause basis parameter value in SQL Server-CodePudding

I wish to set a condition while making use of the WHERE clause basis @USER_ID input recorded from the user in my SQL server query, but I guess I am missing something essential here.

DECLARE @USER_ID NVARCHAR(255) = 'a,b,c' -- will be multi-select parameter; can be null as well 


select * from <table_name>

{
if:      @USER_ID is NULL then no WHERE condition
else:    WHERE <table_name>.<col_name> in (SELECT item FROM SplitString(@USER_ID,','))
}

Can someone please help here?

CodePudding user response：

Personally, I would suggest switching to a table type parameter, then you don't need to use STRING_SPLIT at all. I'm not going to cover the creation of said type of parameter here though; there's plenty already out there and the documentation is more than enough to explain it too.

As for the problem, if we were to be using STRING_SPLIT you would use a method like below:

SELECT {Your Columns} --Don't use *
FROM dbo.YourTable YT
WHERE YT.YourColumn IN (SELECT SS.Value
                        FROM STRING_SPLIT(@USER_ID,',') SS)
   OR @USER_ID IS NULL
OPTION (RECOMPILE);

The RECOMPILE in the OPTION clause is important, as a query where @USER_ID has the value NULL is likely to be very different to that where it isn't.

You could use a dynamic SQL approach, but for one parameter, I doubt there will more anything more than a negligible benefit. Using the above is much easier for others to understand as well, and the cost of generating the plan every time the query is run should be tiny for such a simple query.

Using a table type parameter, it would actually likely be more performant (assuming you have a useable index on YourColumn) to use a UNION ALL query like the below:

SELECT {Your Columns} --Don't use *
FROM dbo.YourTable YT
     JOIN @Users U ON YT.YourColumn = U.UserId
UNION ALL
SELECT {Your Columns} --Don't use *
FROM dbo.YourTable YT
WHERE NOT EXISTS (SELECT 1 FROM @Users U);

CodePudding user response：

You could avoid using STRING_SPLIT entirely here:

WHERE ','   @USER_ID   ',' LIKE '%,'   <table_name>.<col_name>   ',%'

CodePudding user response：

SELECT * 
FROM <table_name>
WHERE (1=1)
    AND ((@USER_ID IS NULL AND (1=1)) -- always true
        OR (<table_name>.<col_name> in (SELECT [value] FROM SplitString(@USER_ID,',')))
    )