Solved

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

Posted on 2010-11-10
5
674 Views
Last Modified: 2012-05-10
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?
Thanks.      

0
Comment
Question by:quasar_ee
5 Comments
 
LVL 32

Accepted Solution

by:
Erick37 earned 500 total points
ID: 34104976
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)
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34104983
If you give your parameters a default value of null you can use the ISNULL or COALESCE functions in your WHERE clause.

Ex:

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

This basically will use the column if the @Value parameter is null.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 34106529
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34111059
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.
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34111379
I have found that using
where (@param IS NULL OR @param=cColumn)
out performs ISNULL or COALESCE
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question