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?

Answer 1

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)