Change CASE in WHERE?-CodePudding

I'm trying to find a way to change the following statement in something more performance-wise:

DECLARE @ID_1 int = -1,     @ID_2 int = 123, @ID_3 int = -1

SELECT ...
    FROM...
    where tblA.ID1 = case when @ID_1 = -1 THEN tblA.ID1 ELSE @ID_1 end
        AND tblB.ID2 = case when @ID_2 = -1 THEN tblB.ID2 ELSE @ID_2 END
        AND tblC.ID3 = case when @ID_3 = -1 THEN tblB.ID3 ELSE @ID_3 END
        AND ....

This works, but the performance is terrible sometimes.

Thank you!

CodePudding user response：

Don't use CASE on a column, it's not SARGable. Use AND and OR logic:

SELECT ...
FROM ...
WHERE (tblA.ID1 = @ID_1 OR @ID_1 = -1) --I would recommend uses NULL for your "catch-all"
  AND (tblB.ID2 = @ID_2 OR @ID_2 = -1) 
  AND (tblC.ID3 = @ID_3 OR @ID_3 = -1)

Due to the "catch-allness" or "kitchen sinkness" of your query, you will likely also want to add RECOMPILE to the OPTION clause of your query to avoid reuse of cached plans that aren't relevant to values of the parameters.

Alternatively, you could use Dynamic SQL and only include the clauses you need for a value of the parameter. I assume here, that at least one of your parameters must have a value other than -1 (as this'll error otherwise) and you are on SQL Server 2017 (and thus have access to CONCAT_WS).

DECLARE @ID_1 int = -1,
        @ID_2 int = 123,
        @ID_3 int = -1;

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL = N'SELECT ...'   @CRLF  
           N'FROM ...'   @CRLF   
           N'WHERE '   CONCAT_WS(@CRLF   N'  AND ',
                                 CASE @ID_1 WHEN -1 THEN NULL ELSE N'tblA.ID1 = @ID_1' END,
                                 CASE @ID_2 WHEN -1 THEN NULL ELSE N'tblB.ID2 = @ID_2' END,
                                 CASE @ID_3 WHEN -1 THEN NULL ELSE N'tblC.ID3 = @ID_3' END)   N';';

--PRINT @SQL; --Your best friend

EXEC sp_executesql @SQL, N'@ID_1 int, @ID_2 int, @ID_3 int', @ID_1, @ID_2, @ID_3;

The advantage of this is that you can used cached plans here, and the RDBMS will likely be able to make more informed choices based on the query being run, however, they are harder to maintain (especially if you don't understand how dynamic SQL works to start with).