SQL Server Pattern – Field = Parameter OR Parameter IS NULL

execution-planplan-cachesql server

I am aware of the parameter sniffing issues associated with stored procedures written with a predicate like the following:

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    SELECT [Field] FROM [dbo].[Table]
    WHERE [Field] = @Parameter 
    OR @Parameter IS NULL;
END;

Depending on the value of the parameter, Scalar or NULL on first execution, a plan is cached that will likely be sub optimal for the opposite value.

Assuming [Field] is scalar, and the clustering index on a table. What are the pros and cons to the following approaches to writing a stored procedure(s) to support the query:

Conditioned selects in same stored procedure

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    IF(@Parameter IS NOT NULL) BEGIN;
        SELECT [Field]
        FROM [dbo].[Table]
        WHERE [Field] = @Parameter;
    END;
    ELSE BEGIN;
        SELECT [Field]
        FROM [dbo].[Table];
    END;
END;

Dynamic SQL within stored procedure

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    DECLARE @sql NVARCHAR(MAX) = N'';
    SET @sql += N'SELECT [Field]'
    SET @sql += N'FROM [dbo].[Table]';

    IF(@Parameter IS NOT NULL) BEGIN;
        @sql += N'WHERE [Field] = @Parameter';
    END;

    SET @sql += N';';

    EXEC sp_executesql @sql N'@Parameter INT', @Parameter;
END;

Separate stored procedures

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    SELECT [Field]
    FROM [dbo].[Table]
    WHERE [Field] = @Parameter;
END;

CREATE PROCEDURE [dbo].[GetAll] AS BEGIN;
    SELECT [Field]
    FROM [dbo].[Table];
END;

Best Answer

They’re all excellent. Really. They all have the same impact of having two plans in the cache, which is what you want.

As you get more and more parameters, you will find the Dynamic SQL option is clearest, even though it looks scarier to beginners.

If this were a function I’d suggest avoiding multi-statement options, so that the QO could do its stuff more nicely.