SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard:

If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?

And if on the following execution, the value of the parameter changes, will it re-compile and re-cache the stored procedure, since a different branch of the code must be executed? (This query is quite expensive to compile.)

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?

No, it caches all versions. Or rather, it caches one version with all paths explored, compiled with the first set of passed in variables. Cardinality estimation for all plans will be done using them. This can be extra bad if some passed in values are NULL.

Here's a quick demo, using the Stack Overflow database.

Create an index:

CREATE INDEX ix_yourmom ON dbo.Users (Reputation) INCLUDE (Id, DisplayName);
GO

Create a stored procedure with an index hint that points to an index that doesn't exist, in branched code.

CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS 
BEGIN

    IF @Reputation = 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;
    END;
    
    IF @Reputation > 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = ix_yourdad)
        WHERE u.Reputation = @Reputation;
    
    END;

END;

If I execute that stored proc looking for Reputation = 1, I get an error.

EXEC dbo.YourMom @Reputation = 1;

Msg 308, Level 16, State 1, Procedure YourMom, Line 14 [Batch Start
Line 32] Index 'ix_yourdad' on table 'dbo.Users' (specified in the
FROM clause) does not exist.

If we fix the index name and re-run the query, the cached plan looks like this:

Inside, the XML will have two references to the @Reputation variable.

A slightly simpler test would be to just get an estimated plan for the stored proc. You can see the optimizer exploring both paths:

And if on the following execution, the value of the parameter changes,
will it re-compile and re-cache the stored procedure, because a
different branch of the code must be executed? (This query is quite
expensive to compile.) Thank you.

No, it will retain the runtime value of the first compilation.

If we re-execute with a different @Reputation:

EXEC dbo.YourMom @Reputation = 2;

From the actual plan:

We still have a compiled value of 1, but now a runtime value of 2.

In the plan cache, which you can check out with a free tool like the one I'm a maintainer on, sp_BlitzCache:

The stored procedure has been called twice, and each statement in it has been called once.

So what do we have? One cached plan for both queries in the stored procedure.

If you want this sort of branched logic, you'd have to call sub-stored procedures:

CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS 
BEGIN

    IF @Reputation = 1
    BEGIN
        
        EXEC dbo.Reputation1Query;

    END;
    
    IF @Reputation > 1
    BEGIN
        
        EXEC dbo.ReputationGreaterThan1Query;
    
    END;

END;

Or dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N''

SET @sql +=
N'
SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u '
IF @Reputation = 1
BEGIN
    SET @sql += N' (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;'
END;

IF @Reputation > 1 
BEGIN

SET @sql += ' WITH (INDEX = ix_yourmom)
        WHERE u.Reputation = @Reputation;'

END;

EXEC sys.sp_executesql @sql;

Hope this helps!

StackExchange Database Administrators Q#185252, answer score: 33