T-SQL: modifying where clause depending on the sproc parameter values

Depending on the sproc parameters value I have to add (or not) additional criteria’s to the where clause of the sproc select statement.
What is the best way to accomplish this?

Who is Participating?
Erick37Connect With a Mentor Commented:
Depending on what you need to do:

--use the parameter to include a column condition
select * from [tablename]
where cColumn = (case when @param=1 THEN somevalue ELSE cColumn END)

--use parameter as a column condition when it is not null
select * from [tablename]
where (@param IS NULL OR @param=cColumn)
Shaun KlineLead Software EngineerCommented:
If you give your parameters a default value of null you can use the ISNULL or COALESCE functions in your WHERE clause.


Create Procedure myproc
@Value as varchar = null
Select *
from table
where value = ISNULL(@Value, value)

This basically will use the column if the @Value parameter is null.
Shaun Kline's info is good, however for large datasets the query is likely to be more efficient if instead of:

WHERE value= COALESCE(@Value,value)

you use:

WHERE value LIKE COALESCE(@Value,'%')

That may sound counter-intuitive since 'LIKE' is usually much slower than =, but in the case of an optional parameter I've found it works quicker.
I have found that using
where (@param IS NULL OR @param=cColumn)
out performs ISNULL or COALESCE
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.