Prevent parameter sniffing in stored procedures SQL Server 2008?

Question

I have started creating a stored procedure that will search through my database table based on the passed parameters. So far I already heard about potential problems with kitchen sink parameter sniffing. There are a few articles that helped understand the problem but I'm still not 100% that I have a good solution. I have a few screens in the system that will search different tables in my database. All of them have three different criteria that the user will select and search on. First criteria are Status that can be Active,Inactive or All. Next will be Filter By, this can offer different options to the user depends on the table and the number of columns. Usually, users can select to filter by Name,Code,Number,DOB,Email,UserName or Show All. Each search screen will have at least 3 filters and one of them will be Show All. I have created a stored procedure where the user can search Status and Filter By Name,Code or Show All. One problem that I have is Status filter. Seems that SQL will check all options in where clause so If I pass parameter 1 SP returns all active records if I pass 0 then only inactive records. The problem is if I pass 2 SP should return all records (active and inactive) but I see only active records. Here is an example:

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Search_Master]
  @Status BIT = NULL,
  @FilterBy INT = NULL,
  @Name VARCHAR(50) = NULL,
  @Code CHAR(2) = NULL
WITH RECOMPILE
AS 
DECLARE @MasterStatus INT;
DECLARE @MasterFilter INT;
DECLARE @MasterName VARCHAR(50);
DECLARE @MasterCode CHAR(2);
SET @MasterStatus = @Status;
SET @MasterFilter = @FilterBy;
SET @MasterName = @Name;
SET @MasterCode = @Code;
SELECT RecID, Status, Code, Name
    FROM Master
    WHERE 
        (
            (@MasterFilter = 1 AND Name LIKE '%'+@MasterName+'%')
            OR 
            (@MasterFilter = 2 AND Code = @MasterCode)
            OR
            (@MasterFilter = 3 AND @MasterName IS NULL AND @MasterCode IS NULL)
        )
        AND
        (
            (@MasterStatus != 2 AND MasterStatus = @Status)
            OR
            (@MasterStatus = 2 AND 1=1)
        );

Other than problem with Status filter I'm wondering if there is any other issues that I might have with parameter sniffing? I found a blog that talks about preventing sniffing and one way to do that is by declaring local variables. If anyone have suggestions or solution for Status filter please let me know.

Answer 1

On your Status issue, I believe the problem is that your BIT parameter isn't behaving as you're expecting it to. Here's a quick test to demonstrate:

DECLARE @bit BIT;

SET @bit = 2

SELECT @bit AS [2=What?];

--Results
+---------+
| 2=What? |
+---------+
|       1 |
+---------+

From our friends at Microsoft:

Converting to bit promotes any nonzero value to 1.

When you pass in your parameter as 2, the engine does an implicit conversion from INTEGER to BIT, and your non-zero value becomes a 1.

You'll likely want to change the data type on that parameter, then use some conditional logic inside your procedure to deal with the various possible values as you want them to be handled.

On the issue of parameter sniffing, 1) read the article Sean suggests in the comments, but 2) if you keep that WITH RECOMPILE on your procedure, parameter sniffing can't happen.

The issue (but still read the article) is that SQL Server uses the first set of parameters you send through the proc to store an execution plan, but subsequent parameters require substantially different plans. Adding WITH RECOMPILE is forcing a new execution plan on every iteration, which has some overhead, but is may well be exactly what you want to do in your situation.

As a closing thought, SQL Server 2008 ended mainstream support in 2014 and extended support ends on 7/9/2019. An upgrade might be a good idea.