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.