I have a stored procedure of the form:
CREATE PROCEDURE StoredProcedure @SearchString NVARCHAR(MAX), @ComparisonType INT AS BEGIN SELECT A.Value1, B.Value1, C.Value1, ... FROM TableA as A INNER JOIN TableB as B on B.Key1 = A.Key1 INNER JOIN TableC as C on C.Key2 = B.Key2 WHERE A.DateValue > '2020-10-01' And 1 = CASE @SearchString WHEN N'' THEN 1 ELSE CASE @ComparisonType WHEN 0 THEN -- potentially expensive query 1 referencing TableC WHEN 1 THEN -- potentially expensive query 2 referencing TableC WHEN 6 THEN -- potentially expensive query 3 referencing TableC WHEN 8 THEN -- potentially expensive query 4 referencing TableC WHEN 10 -- potentially expensive query 5 referencing TableC ELSE 0 END END END
Even when it gets called with
@SearchString set to
N'' the query takes a minute and half to execute. If I remove the "potentially expensive query" parts then the query returns the expected data virtually instantaneously.
From what I've read, the above shouldn't evaluate anything in the
ELSE part of the outer
CASE statement as
N'' so why does it act as though it is?
If this is working as expected then how do I code this to force SQL not to execute the "potentially expensive query" parts?
I'm trying to understand why they appear to affect the main query despite my efforts to ensure that they aren't called at all.