IFs don't prevent sniffs.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com

Setup:
    Download BabbyNames.bak.zip (11.6 MB database backup)
    https://github.com/LitKnd/BabbyNames/releases/tag/v1.0

*****************************************************************************/

use master;
GO

IF DB_ID('BabbyNames') IS NOT NULL
BEGIN
	ALTER DATABASE BabbyNames
		SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
END
GO

RESTORE DATABASE BabbyNames
	FROM DISK=N'S:\MSSQL\Backup\BabbyNames.bak'
	WITH
        MOVE 'BabbyNames' TO 'S:\MSSQL\Data\BabbyNames.mdf',
        MOVE 'BabbyNames_log' TO 'S:\MSSQL\Data\BabbyNames_log.ldf',
		REPLACE,
		RECOVERY;
GO


USE BabbyNames;
GO

ALTER TABLE agg.FirstNameByYearState
    ADD Flag TINYINT NOT NULL 
        CONSTRAINT Flag_DEFAULT DEFAULT (0)
GO

CREATE NONCLUSTERED INDEX ix_FirstNameByYearState_Flag on
    agg.FirstNameByYearState (Flag) INCLUDE (FirstNameId);
GO

ALTER TABLE agg.FirstNameByYearState
    ADD CONSTRAINT Flag_CHECK CHECK (Flag IN (0,1,2,3,4,5));
GO

/* "Flag" a lot of rows */
UPDATE agg.FirstNameByYearState
    SET Flag = 1
WHERE FirstNameId > (90000)
GO


SET STATISTICS IO, TIME OFF;
GO
SET XACT_ABORT, NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

ALTER DATABASE [BabbyNames] SET QUERY_STORE = ON
GO
ALTER DATABASE [BabbyNames] SET QUERY_STORE 
    (OPERATION_MODE = READ_WRITE, 
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 180), MAX_STORAGE_SIZE_MB = 1024
    )
GO



DROP PROC IF EXISTS dbo.ReviewFlags;
GO
CREATE PROC dbo.ReviewFlags
    @Flag TINYINT 
AS
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5)
            RAISERROR ('@Flag must be a value between 1 and 5', 11, 1);

        SELECT
            fnbd.FirstNameId,
            fn.FirstName,
            fnbd.Gender
        FROM agg.FirstNameByYearState as fnbd
        JOIN ref.FirstName as fn on
            fnbd.FirstNameId = fn.FirstNameId
        WHERE fnbd.Flag = @Flag
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
GO

/* First run the procedure this way... 
it hits the IF validation and raises the error */
EXEC dbo.ReviewFlags @Flag = null;
GO


/* Run this with actual plans and look at the value that 
@Flag was optimized for */
EXEC dbo.ReviewFlags @Flag = 1;
GO

EXEC dbo.ReviewFlags @Flag = 1 WITH RECOMPILE;
GO


/* Dynamic SQL */

DROP PROC IF EXISTS dbo.ReviewFlags;
GO
CREATE PROC dbo.ReviewFlags
    @Flag TINYINT 
AS
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5)
            RAISERROR ('@Flag must be a value between 1 and 5', 11, 1);

        DECLARE @dsql NVARCHAR(MAX) = N'
        SELECT
            fnbd.FirstNameId,
            fn.FirstName,
            fnbd.Gender
        FROM agg.FirstNameByYearState as fnbd
        JOIN ref.FirstName as fn on
            fnbd.FirstNameId = fn.FirstNameId
        WHERE fnbd.Flag = @Flag';

        EXEC sp_executesql @stmt = @dsql, @params = N'@Flag TINYINT', @Flag=@Flag;
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
GO

EXEC dbo.ReviewFlags @Flag = null;
GO
EXEC dbo.ReviewFlags @Flag = 1;
GO

/* Optimize FOR */

DROP PROC IF EXISTS dbo.ReviewFlags;
GO
CREATE PROC dbo.ReviewFlags
    @Flag TINYINT 
AS
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5)
            RAISERROR ('@Flag must be a value between 1 and 5', 11, 1);

        SELECT
            fnbd.FirstNameId,
            fn.FirstName,
            fnbd.Gender
        FROM agg.FirstNameByYearState as fnbd
        JOIN ref.FirstName as fn on
            fnbd.FirstNameId = fn.FirstNameId
        WHERE fnbd.Flag = @Flag
            OPTION (OPTIMIZE FOR (@Flag = 1))
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
GO


EXEC dbo.ReviewFlags @Flag = null;
GO
EXEC dbo.ReviewFlags @Flag = 1;
GO