CODEWITHSUNDEEP
sql-serverazuret-sql
Philip
Philip

Reputation: 2628

Stored procedure where statement containing many parameters

I'm developing a lot of stored procedures, and a great deal of them have a where clause similiar to the below. 

WHERE
    (CE.EnquiryDate >= @StartDate or @StartDate is null) and
    (CE.EnquiryDate <= @EndDate or @EndDate is null) and
    (CE.ClientID = @ClientID or @ClientID is null)

What concerns me is performance and the execution plans that are generated potentially causing issues down the track, i.e. if a plan is cached on first execution of the sp when only a clientid is passed then it is obviously going to be different to when a ClientID is not passed and a StartDate and EndDate are.

Am I going to be better off here splitting these off into multiple stored procedures or using OPTION (RECOMPILE) to get a new plan on each run? Just wondering what the best approach is in the early stages of development.

Upvotes: 0

Views: 119

Answers (2)

St&#233;phane CLEMENT
St&#233;phane CLEMENT

Reputation: 372

Try this :

WHERE
    (CE.EnquiryDate >= ISNULL(@StartDate,CE.EnquiryDate)) and
    (CE.EnquiryDate <= ISNULL(@EndDate,CE.EnquiryDate)) and
    (CE.ClientID = ISNULL(@ClientID,CE.ClientID))

This can be used because of the "<=",">=" and "=" : not working with "<" or ">".

The goal is to avoir OR operator... Witch are so so slow. See execution plan.

See https://en.wikipedia.org/wiki/Boolean_algebra for further details about avoiding OR...

X OR Y = NOT (NOT X AND NOT Y)

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89256

The standard treatment of this question is Erland Sommarskog's Dynamic Search Conditions in T-SQL. Read that for a full discussion of the techniques and tradeoffs.

Since SQL 2008 SQL Server will optimize the query plan for this kind of query when using OPTION RECOMPILE. The predicates corresponding to null parameters will be removed from the query during compilation. So OPTION RECOMPILE is my default here, but if you have some search patterns you need to be super cheap, you may special-case them before a catch-all using OPTION RECOMPILE.

Something like:

IF (@StartDate is not null and @ClientId is not null)
BEGIN

    SELECT * 
    FROM T 
    WHERE
        (CE.EnquiryDate >= @StartDate)  and
        (CE.EnquiryDate <= @EndDate or @EndDate is null) and
        (CE.ClientID = @ClientID )

    RETURN
END


SELECT * 
FROM T 
WHERE
    (CE.EnquiryDate >= @StartDate or @StartDate is null) and
    (CE.EnquiryDate <= @EndDate or @EndDate is null) and
    (CE.ClientID = @ClientID or @ClientID is null)
WITH (OPTION RECOMPILE)

Upvotes: 2

Related Questions