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

TopAnswers Databases
Top Answer
meme (imported from SE)
> 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 passed in variables. 

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][1] looks like this:

[![Nuts][2]][2]

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

    <ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" />

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

[![Nuts][3]][3]

> 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][4]:

    <ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(2)" />

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 my company develops, [sp_BlitzCache][5]:

[![Nuts][6]][6]

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!

  [1]: https://www.brentozar.com/pastetheplan/?id=HJR9gxCFW
  [2]: https://i.stack.imgur.com/jFSeB.jpg
  [3]: https://i.stack.imgur.com/MPatW.jpg
  [4]: https://www.brentozar.com/pastetheplan/?id=S1asZgCFZ
  [5]: http://firstresponderkit.org
  [6]: https://i.stack.imgur.com/rPyQs.jpg
1 Answer
