Optional Arguments in WHERE Clause

Question

Lets suppose there is a stored procedure that has 3 params. Out of all the possibilities, I'm looking to achieve this with a single WHERE clause without getting out of control with using () AND () OR () too much...

Example:

    //Params
@CITY VARCHAR(100) = NULL,
@GENDER VARCHAR(100) = NULL,
@AGE VARCHAR(100) = NULL

I suppose you can do it using IF BEGIN ... END for each Variable if Exists, but that makes the code alot longer than desired..

This method below won't work because its way too long (there are about 10 different fields like this, but the example is only 3.) and i'm not sure if it even directly pulls up distinctive values...

SELECT NAME FROM TABLE 
WHERE (
(CITY=@CITY AND GENDER=@GENDER AND AGE=@AGE)
OR (CITY=@CITY AND GENDER=@GENDER)
OR (GENDER=@GENDER AND AGE=@AGE)
OR (CITY=@CITY AND AGE=@AGE)
OR (CITY=@CITY)
OR (GENDER=@GENDER)
OR (AGE=@AGE)
)

Is there an even shorter more efficient way to do this?

If yes, it is preferable for the method to be compatible with JOIN's also.

Side-note: when defining a `VARCHAR()` - as a parameter to a stored proc or a local variable - should **always** have a **length** defined! Otherwise you might end up with `VARCHAR(1)` strings inadvertently... — marc_s, Apr 17 '12 at 05:42
Optional fields meaning if there is no or `NULL` value, then don't show it in the Where clause, is it possible? — Control Freak, Apr 17 '12 at 05:49
Read this: [Dynamic Search Conditions in T-SQL](http://sommarskog.se/dyn-search-2008.html) — Remus Rusanu, Apr 17 '12 at 06:10

score 83 · Accepted Answer · answered Apr 17 '12 at 05:56

83

Alternatively to the ISNULL / COALESCE options, you can test the parameters for being null:

SELECT NAME  
FROM TABLE  
WHERE  
    (@City IS NULL OR City = @City)
AND 
    (@Gender IS NULL OR Gender = @Gender)
AND 
    (@Age IS NULL OR Age = @Age)

answered Apr 17 '12 at 05:56

cjk

45,739
9
81
112

7

I know it's a bit too late for a comment, but please be careful with this approach as SQL Server does not guarantee the order of the evaluation (it won't for sure do the short-circuit). So you might as well end up SQL Server doing all the unnecessary checks. – Ilya Chernomordik Feb 09 '15 at 09:49
4

@IlyaChernomordik: And flushing performance down the hill. – Victor Zakharov Mar 25 '15 at 16:42
1

@IlyaChernomordik so what would be a better way of doing this? Thought of dynamic sql but it bring other problems like maintenance, code clearance, etc. There's no other better way? – Iúri dos Anjos Jul 17 '17 at 19:11
Not that I know of at least :) I would use dynamic SQL if it gets complex enough and begins affecting performance – Ilya Chernomordik Jul 18 '17 at 07:09
1

using OR and Dynamic sql Query lead us to performance hit, It seems the best bet is using IF ..ELSE or Case statement ,I think By this way two execution plan would be make and would be cached , I had such a question, please take a look at it for getting started . https://stackoverflow.com/questions/46386090/building-dynamic-query-using-case-in-where-clause – siamak Sep 24 '17 at 09:10
it works ok with small results which have to be filtered. but the performance is really bad with joins on large tables. (informix) – d2k2 Dec 06 '19 at 09:54
cjk , Hello , I was waondering how we do it with if we have to deal with a list like of String ? – Meryemb Dec 28 '20 at 16:27
@cjk it make my Query 10x slower! – AminM Jan 04 '21 at 16:42

score 20 · Answer 2 · answered Apr 17 '12 at 05:54

20

what about this?

SELECT
    NAME
FROM TABLE 
WHERE CITY = COALESCE(@CITY, CITY)
    AND GENDER = COALESCE(@GENDER, GENDER)
    AND AGE = COALESCE(@AGE, AGE)

answered Apr 17 '12 at 05:54

silly

7,789
2
24
37

2

Beautiful! Hopefully, SQL Server is able to optimize away the conditions if null variables are encountered. Would be interesting to test. – l33t Sep 10 '16 at 19:23
1

This is nice, but will not include a record if, say, `CITY` and `@CITY` are both null, because null is not equatable to null (unless `ANSI_NULLS` is set to OFF). – MCattle Jul 30 '19 at 17:44

score 7 · Answer 3 · answered Apr 17 '12 at 05:53

7

Try something like this:

SELECT NAME 
FROM TABLE 
WHERE 
    City = IsNull(@City, City) AND
    Gender = IsNull(@Gender, Gender) AND
    Age = IsNull(@Age, Age)

OR:

SELECT NAME 
FROM TABLE 
WHERE 
    (City = @City OR @City IS NULL) AND
    (Gender = @Gender OR @Gender IS NULL) AND
    (Age = @Age OR @Age IS NULL)

answered Apr 17 '12 at 05:53

Greg

8,574
21
67
109

i think your first example may cut it, let me check it out.. brb. – Control Freak Apr 17 '12 at 06:05
In regards to the first option....does NULL equal NULL? – Myles J Apr 03 '13 at 12:39
`Select * From Table Where NULL IS NULL` will return all rows. That is ok in this context because it means that you do not want to filter by the argument if you pass it a `NULL` value. – Greg Apr 03 '13 at 13:04
Yes but Select * From Table Where NULL = NULL will not include the rows with null column values. Test it out on a table that has a nullable column containing NULLs. e.g:SELECT * FROM mytable WHERE mycolumn = IsNull(null, mycolumn) – Myles J Apr 03 '13 at 13:15
That *City = IsNull(@City, City)* format never occurred to me before. Nice and clean looking. – Justin Dec 08 '17 at 09:41

score 2 · Answer 4 · answered Apr 17 '12 at 06:34

2

SELECT NAME   
FROM TABLE   
WHERE       
  City = case when isnull(@City ,'') = '' then City
                        else @City end
AND      
  Gender = case when isnull(@Gender ,'') = '' then Gender
                        else @Gender end
AND  
  Age = case when isnull(@Age ,0) = 0 then Age
                        else @Age end

answered Apr 17 '12 at 06:34

userGS

220
1
7

1

I doubt that the City=City condition and in general Column =Column Condition is an ineffective condition , for example null = null could lead us to UNKNOWN and at the other hand if our column would be Not null , we end up with a full scan table for column = column condition . – siamak Sep 24 '17 at 09:28

John Dewey · Answer 5 · 2012-04-17T05:54:56.457

1

Possibly this:

create procedure myProc
    --Params
@CITY VARCHAR(100) = NULL,
@GENDER VARCHAR(100) = NULL,
@AGE VARCHAR(100) = NULL
as

SELECT NAME FROM [TABLE]
WHERE ISNULL(CITY,'')=ISNULL(@CITY,ISNULL(CITY,''))
AND ISNULL(GENDER,'')=ISNULL(@GENDER,ISNULL(GENDER,''))
AND ISNULL(AGE,'')=ISNULL(@AGE,ISNULL(AGE,''))
go

Assuming the columns in the WHERE clause are nullable, using ISNULL to avoid null comparison.

edited Apr 17 '12 at 05:54

answered Apr 17 '12 at 05:45

John Dewey

6,985
3
22
26

uggghhh.. i wanted to avoid doing it that way.. anything like 'IsNull'? – Control Freak Apr 17 '12 at 05:48

Optional Arguments in WHERE Clause

5 Answers5

Linked

Related