What is the most efficient way to optimize a filter based on parameters on a SQL Server stored procedure?

Right now I have something like:

CREATE PROCEDURE [dbo].[sp_GetFilteredInformation]
    @pItem nvarchar(max) = NULL, 
    @pCity nvarchar(max) = NULL,
    @pSerialN nvarchar(max) = NULL, 
    @pPromise datetime = NULL,
    @pSalesOrder nvarchar(max) = NULL, 
    @pLineNumber int = NULL

    DECLARE @vQuery nvarchar(max)

    IF (@pItem IS NOT NULL) 
    BEGIN 
        SET @vQuery += 'AND ITEM LIKE '' + @pItem + ''' 
    END

    IF (@pCity IS NOT NULL) 
    BEGIN 
        SET @vQuery += 'AND CITY LIKE '' + @pCity + ''' 
    END

… and so on, so in the end I’ll have

SELECT * 
FROM TABLE 
WHERE 1 = 1 + @vQuery

I think this is going to work, but it doesn’t seems efficient to me. Is there a way to optimize this process and filter information with multiple parameters, with the option of some of them being null?

Answer

The most efficient method to do this type of kitchen-sink query is actually the way you are doing it now, except that you should properly parameterize each filter.

This is because a single plan will be cached for every possible combination of filters. This means that whenever that combination of filters is used again, even with different values, a cached plan will be used.

Whereas if you use OPTION(RECOMPILE), a new plan is generated on every run. And OPTION(OPTIMIZE FOR UNKNOWN) will usually just get you an overall not-that-great plan.

So you parameterize it with sp_executesql, like this

CREATE PROCEDURE [dbo].[sp_GetFilteredInformation]
    @pItem nvarchar(max) = NULL, 
    @pCity nvarchar(max) = NULL,
    @pSerialN nvarchar(max) = NULL, 
    @pPromise datetime = NULL,
    @pSalesOrder nvarchar(max) = NULL, 
    @pLineNumber int = NULL

DECLARE @vQuery nvarchar(max) = '
SELECT *
FROM YourTable
WHERE 1=1
';

IF (@pItem IS NOT NULL) 
    SET @vQuery += 'AND ITEM LIKE @pItem
';

IF (@pCity IS NOT NULL) 
    SET @vQuery += 'AND CITY LIKE @pCity
';

-- etc

-- for testing you can use PRINT @vQuery

EXEC sp_executesql
    @vQuery,

  N'@pItem nvarchar(max), 
    @pCity nvarchar(max),
    @pSerialN nvarchar(max), 
    @pPromise datetime,
    @pSalesOrder nvarchar(max),
    @pLineNumber int',

    @pItem = @pItem,
    @pCity = @pCity,
    @pSerialN = @pSerialN,
    @pPromise = @pPromise,
    @pSalesOrder = @pSalesOrder,
    @pLineNumber = @pLineNumber;