Ignoring a NULL parameter in T-SQL

Question

I want to be able to pass in a list of parameters, and ignore the ones which are NULL. So that the query is in effect pretending that the filter isn't there and ignoring it.

I was doing it like this:

(@thing IS NULL or Thing=@thing)

Is this right, and if so, would it perform badly? It's seems to be a lot slower than constructing the SQL separately.

What's the optimal way to do this?

FIXED! See Marc Gravell's answer. In summary using IS NULL many times is a big performance hit.

Marc Gravell · Accepted Answer · 2009-02-10T15:29:28.123

22

Once you get more than a couple of these, then yes: it starts to get pretty slow. In such cases, I tend to use generated TSQL - i.e.

DECLARE @sql nvarchar(4000)
SET @sql = /* core query */

IF @name IS NOT NULL
    SET @sql = @sql + ' AND foo.Name = @name'

IF @dob IS NOT NULL
    SET @sql = @sql + ' AND foo.DOB = @dob'

// etc

EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime',
        @name, @dob

etc

Note that sp_ExecuteSQL caches query-plans, so any queries with the same args can potentially re-use the plan.

The downside is that unless you sign the SPROC, the caller needs SELECT permissions on the table (not just EXEC permissions on the SPROC).

edited Feb 10 '09 at 15:29

answered Feb 10 '09 at 14:21

Marc Gravell

1,026,079
266
2,566
2,900

Sorry, AS noob question, what do you mean sign the sproc? Surely I need a select anyway to define the table ? – Damien Feb 10 '09 at 14:42
also how do I handle the fact that it may include an extra and – Damien Feb 10 '09 at 14:45
1

The table etc would be part of the /* core query */ bit (that you need to fill in). If you don't already have a `WHERE`, you can either add a dummy ' WHERE 1 = 1', or you'll have to use something like 'STUFF(...)' (or other string manipulation) to change the first AND to a WHERE – Marc Gravell Feb 10 '09 at 14:58
(which is easiest if you have a separate variable for the filters - @where, for example, and concatenate as the penultimate step) – Marc Gravell Feb 10 '09 at 14:59
Re signing: http://msdn.microsoft.com/en-us/library/bb669102.aspx - this allows the user to call the SP (and have it work) even without SELECT access to the tables - without signing, the EXEC will use the user's permissions. Note: I've **never** had to sign something in real life... maybe ignore it. – Marc Gravell Feb 10 '09 at 15:03
Excellent! Worked perfectly and performs much better! – Damien Feb 10 '09 at 15:18

score 12 · Answer 2 · answered Feb 10 '09 at 14:12

12

I would handle it this way.

WHERE Thing = ISNULL(@Thing, Thing)

If you're just using the parameter as a filter on the where clause, this will work very well. It will ignore the parameter if it is null.

answered Feb 10 '09 at 14:12

Brendan Enrick

4,277
2
26
40

1

I've found, like the OP, that with more than a couple of these it quickly kills the performance, as it can't easily identify the most useful index etc. – Marc Gravell Feb 10 '09 at 14:25
I agree that the ISNULL trick is likely to be a performance issue - I would unroll it as an OR to give the optimiser a better chance. Also beware that ISNULL adopts the data type of the first parameter, so could have strange side effects. COALESCE probably a better choice. – Kristen Feb 14 '09 at 10:42
2

Avoid! Do not use this example. If you pass in null for "@Thing" and want to pull all values (including null), this will NOT work. If "Thing" is null, you get null = null, which everyone knows evaluates to false. – MikeTeeVee Oct 07 '11 at 19:29
@MikeTeeVee Yes, you're correct. If that is your circumstance, then yes you should use a different approach. If you don't allow nulls on the column then this approach is fine. (if you're doing tons of these, as with many things not used in moderation, then yes, you will get a performance hit) – Brendan Enrick Nov 07 '11 at 15:24

score 7 · Answer 3 · answered Feb 10 '09 at 14:57

7

I generally use

WHERE (id = @id OR @id IS NULL)
AND (num = @num OR @num IS NULL)

etc.

answered Feb 10 '09 at 14:57

atfergs

1,674
1
11
17

score 2 · Answer 4 · answered Feb 10 '09 at 14:53

A technique I’ve used in the past for this scenario is to utilize the COALESCE function as part of my WHERE clause. Books Online will provide more in depth info on the function, but here’s a snippet of how you can use it in the scenario you described:

create procedure usp_TEST_COALESCE
(
    @parm1 varchar(32) = null,
    @parm2 varchar(32) = null,
    @parm3 int = null
)
AS

SELECT * 
FROM [TableName]
WHERE Field1 = COALESCE(@parm1, Field1)
AND Field2 = COALESCE(@parm2, Field2)
AND Field3 = COALESCE(@parm3, Field3)

The COALESCE function will return the first non-null expression from its arguments. In the example above, if any of the parameters are null, the COALESCE function will use the value in the underlying field.

One important caveat to using this technique is that the underlying fields in the table (that make up your where clause) need to be non-nullable.

With the large datasets I use I find this method (while clean) to be slower than (@parm2 IS NULL OR Field2 = @parm2) — BGilman, Mar 06 '18 at 16:24
Hi what if the Field1 is a NULLable value; then would it be good practice to write WHERE COALESCE(Field1,'')=COALESCE(@parm1,Field1,'')? — variable, May 16 '19 at 06:29

score 1 · Answer 5 · answered Nov 30 '10 at 14:56

Look at the following link in the section titled "The Case Study: Searching Orders". This explores all options in depth and should give you an excellent overview of the costs associated with each of these options. Warning, be very careful when using COALESCE it may not return what you think it is.

Regards,

Tim

score 1 · Answer 6 · answered Feb 10 '09 at 14:11

1

I'm not sure if it is the 'optimal' way, but this is exactly what I do in my stored procedures for the same purposes. My gut feeling is that this is faster than a dynamically created query purely from an execution plan standpoint. The other option is to create a query for each combination of these "flags" that you are passing in, but that really isn't that scalable.

answered Feb 10 '09 at 14:11

Sean Bright

118,630
17
138
146

Per execution plan - things like sp_ExecuteSQL will cache and re-use query plans; so a re-used *specialized* plan can easily out-perform a generalized static version... – Marc Gravell Feb 10 '09 at 14:28
I've never noticed an measurable difference and I'll always take readability over a few extra millis (unless there are SLA requirements to the contrary). – Sean Bright Feb 10 '09 at 14:40
FWIW I have earned plenty of consultancy money just helping coders move from huge WHERE clauses to parametrised sp_ExecuteSQL constructions as being the quickest way to improving performance. Where coders typically use a WHERE clause of concatenated "bits" changing to sp_ExecuteSQL is the answer – Kristen Feb 14 '09 at 10:46

score 0 · Answer 7 · answered Jan 10 '10 at 21:19

Thanks, This was helpful. I have decided to use the sp_ExecuteSQL method due to the potential performance advantages mentioned. I have a slightly different take on it which you may find helpful.

DECLARE @sql nvarchar(4000) 
DECLARE @where nvarchar(1000) =''

SET @sql = 'SELECT * FROM MyTable'

IF @Param1 IS NOT NULL 
    SET @where = @where + ' AND Field1 = @Param1'

IF @Param2 IS NOT NULL 
    SET @where = @where + ' AND Field2 = @Param2' 

IF @Param3 IS NOT NULL 
    SET @where = @where + ' AND Field3 = @Param3' 

-- Add WHERE if where clause exists, 1=1 is included because @where begins with AND
IF @where <> ''
    SET @sql = @sql + ' WHERE 1=1' + @where

--Note that we could also create order parameters and append here
SET @sql = @sql + ' ORDER BY Field1'

score 0 · Answer 8 · answered Feb 10 '09 at 14:13

0

This is the method I typically use. I see no reason for it to be inefficient, as the statement should short-circuit to true if @thing is null, and would therefore not require a table scan. Do you have any evidence that this comparison is slowing your query? If not, I would not worry about it.

answered Feb 10 '09 at 14:13

Karmic Coder

17,569
6
32
42

yes 10 years later this is still true at least in MS SQL Server . Like you I wrongly assumed "statement should short-circuit to true if @thing is null, and would therefore not require a table scan. "... as a result *each* request on 400K lines with 15 parameters takes 10+ SECONDS ! – Emmanuel Gleizer Jan 14 '20 at 08:54

score 0 · Answer 9 · answered Feb 10 '09 at 14:13

when you declare the parameters if you set a value to them such as null in your case you do not need to pass a value in to them unless of course you need to. I use this ability to flag if another query needs to be run is special cases when the parameter is not null

I typically just check it like this

IF field IS NULL

score 0 · Answer 10 · answered May 16 '19 at 06:36

0

If the Thing (column value) is also Nullable, then use the foll. approach:

WHERE COALESCE(Thing,'')=COALESCE(@thing,Thing,'')

answered May 16 '19 at 06:36

variable

8,262
9
95
215

Ignoring a NULL parameter in T-SQL

10 Answers10

Linked