SQL Server where clause using iif in store procedure

Question

I have a store procedure in SQL Server. I have a filtering page in my application, I want to select all CDT_ID, if I do not input the name and lower_age and upper_age, it will show all the CDT_ID. But if I input the name and age, it will show all the CDT_ID where CDT_NAME AND CDT_AGE range is like I input in the filtering column. My query is like in this below:

select CDT_ID from CANDIDATE 
where CDT_NAME LIKE iif(@name is null, '', @name)
AND CDT_NAME between (iif(@lower_age is null, '', @lower_age) and iif(@upper_age is null, '', @upper_age))

The problem is my query result show nothing when I execute my store procedure. And if I run the query without where it shows a lot of CDT_ID. Do you know how to fix my 'where' clauses?

score 0 · Accepted Answer · answered Mar 19 '18 at 06:38

0

More than IIF, COALESCE will work better here. Also when using parameters in WHERE clause, typical syntax for WHERE clause is like

...
WHERE 1=1 AND p=@param

See this answer for details

In your query CDT_AGE was not mentioned. I took liberty to correct it.

Here's a revised query

select 
    CDT_ID 
from CANDIDATE 
where 
    1=1
        AND
    CDT_NAME LIKE COALESCE(@name,CDT_NAME)
        AND
    CDT_AGE between COALESCE(@lower_age,0) AND COALESCE(@upper_age,1000)

answered Mar 19 '18 at 06:38

DhruvJoshi

17,041
6
41
60

I would strongly disagree that `WHERE 1=1` is *typical syntax* and even when used for creating dynamic conditions in an application it's still just being lazy. Simply add an `OR`ed condition and start wrapping it into `()`. – dnoeth Mar 19 '18 at 07:34

score 0 · Answer 2 · answered Mar 19 '18 at 07:45

You used CDT_NAME instead of CDT_AGE.

I don't know about your exact business rules, but instead of a proprietary IIF or a Standard SQL compliant COALESCE I would use a simple ORed condition (which might enable indexed access, too):

select CDT_ID 
from CANDIDATE 
where (@name is null and @lower_age is null and @upper_age is null)
   or (    CDT_NAME LIKE @name
       AND CDT_AGE between @lower_age AND @upper_age
      )

If you want to return all rows if either name or age is null you can modify it to:

where (@name is null or @lower_age is null or @upper_age is null)

Or you add some sanity check before the Select and apply a default value. Or run two different Selects based on NULLs.

SQL Server where clause using iif in store procedure

2 Answers2