Sql-server – SQL Server evaluates where condition before or during the run

best practicessql serverstored-procedures

I have a Stored Procedured that looks similar to this:

CREATE PROC Campaign.STP_CampaignTransaction_Get
(
    @id             INT     
    @first_name     VARCHAR(50)     
    @last_name      VARCHAR(50)     
    ,@age           INT     
    ,@gender        INT
)
AS
BEGIN
    SELECT
        [id]        
        ,[first_name]
        ,[last_name]
        ,[age]      
        ,[gender]                   
    FROM dbo.Persons p
    WHERE   (@id IS NULL OR t.id=@id)
        AND (@first_name IS NULL OR t.first_name= @first_name)
        AND (@last_nameIS NULL OR t.last_name = @last_name)
        AND (@age IS NULL OR t.age=@age)
        AND (@gender IS NULL OR t.gender=@gender)
END

Basically as you can see all the parameters they work as filters. Lets say that I send only the @first_name parameter the performance will be the same as checking the parameters once and then running the following query:

SELECT
            [id]        
            ,[first_name]
            ,[last_name]
            ,[age]      
            ,[gender]                   
        FROM dbo.Persons p
        WHERE   t.first_name= @first_name

Or for each row sql will evaluate if my parameters are null or not and go through all the conditions?

NOTE: The question started with a performance discussion in my company that if we should create 2 STPs or only one with filters

Best Answer

As your procedure is written, you cannot pass only the @first_name parameter, since the other parameters has no default value. But I assume you meant them to have NULL as default, or that you will pass NULL for them.

There is nothing special about NULL. SQL Server will create a plan based on the values for the parameters (aka Parameter Sniffing). That plan will then be cached for re-use. I.e., the plan might be OK for when you search for precisely firstname. But it can be a disaster for the next execution, when you search for lastname.

Adding OPTION(RECOMPILE) will allow a "perfect" plan for each execution, but at the cost of creating a plan for each execution.

Or you can use sub-procedures, executing a sub-proc conditionally based on the parameters passed (which is not the same as doing IF and have several queries in the STP_CampaignTransaction_Get procedure!).

Or use dynamic SQL and construct precisely the SQL you need (only include the WHERE condition(s) needed) and then execute precisely that SQL. I.e., you will have different query plans cached, based on the combo of search arguments.

Erland explains this very well in his article, the one that Bijy refers to in his comment, so go check it out!

Related Question