Prevent SQL from apparently executing an expensive sub query 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 &gt; '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 &quot;potentially expensive query&quot; 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 @SearchString is 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 &quot;potentially expensive query&quot; 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.
If you add OPTION (RECOMPILE) to the very end of the WHERE clause so it looks like this, does the procedure run faster now?
WHERE A.DateValue &gt; '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
OPTION (RECOMPILE)

If so, then you likely have a parameter sniffing issue which could be causing bad execution plans to be generated with poor cardinality estimates. If you update your post with your execution plan, then we can pinpoint exactly how that's happening.
If that's the case, this is a good article on Parameter Sniffing and OPTION (RECOMPILE).
Note I usually only use OPTION (RECOMPILE) for debugging to quickly confirm a parameter sniffing issue and try to resolve the underlying problem instead. There are use cases for OPTION (RECOMPILE) however it's a trade-off for minor performance hits since the query gets recompiled every time it's executed.
Brent Ozar has an awesome series of blog posts on Parameter Sniffing issues. Too many to individually link to be honest, so here's the root page that links the main important ones: Parameter Sniffing