title: SQL Server where clause using iif in store procedure
body: <p>I have a store procedure in SQL Server. I have a filtering page in my application, I want to select all <code>CDT_ID</code>, if I do not input the name and lower_age and upper_age, it will show all the <code>CDT_ID</code>. But if I input the name and age, it will show all the <code>CDT_ID</code> where <code>CDT_NAME</code> AND <code>CDT_AGE</code> range is like I input in the filtering column. My query is like in this below:</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>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 <code>CDT_ID</code>. Do you know how to fix my 'where' clauses?</p>