How do I include optional parameters using IF-THEN-ELSE logic in a SQL query?

Question

Here is the create statement for a stored procedure:

Create Procedure SearchCreatedAssignments
(@LessonName Varchar(50), @DateFrom date, @DateTo Date, @LocationCode Varchar(10))
As 
BEGIN

Basically, I want to write a query that searches the database based on the values of the parameters. For example:

Select *
from dbo.test

where (LessonName = @LessonName)
AND (StartDate = @DateFrom)
AND (EndDate = @DateTo) 
AND (LocationCode = @LocationCode)

Fairly simple, right? However, if any of these parameters are null (or contain an empty string), I would like to omit them from the search, and search by only the parameters that are not null. I was thinking something like this:

--if @LocationCode is null OR @LocationCode = '' -> omit @LocationCode from the search

This is obviously pseudo code. How can I do this? Forgive me if this is a simple task; I am new to SQL.

John Cappelletti · Accepted Answer · 2017-04-20 17:16:35Z

4

Consider the following. If a parameter is NULL or empty, the default value will be the field in question

Select *
from dbo.test
where LessonName   = IsNull(NullIf(@LessonName,''),LessonName) 
 AND  StartDate    = IsNull(NullIf(@DateFrom,''),StartDate) 
 AND  EndDate      = IsNull(NullIf(@DateTo,''),EndDate)
 AND  LocationCode = IsNull(NullIf(@LocationCode,''),LocationCode)

answered Apr 20, 2017 at 17:16

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ic3man7019 Over a year ago

So, if the parameter is null (or contains an empty string), is it essentially omitted from the search?

Jorge Campos Over a year ago

@ic3man7019 no, it will just compare the field with itself which will evaluate to true. Most answers do the same.

ahoxha Over a year ago

@ic3man7019 I want to jump in and just point out that it will give the same result as if it were omitted. I use this practice a lot and haven't seen a better one for this kind of queries. And also, many other answers are proposing dynamic sql, I would avoid it whenever it's possible. Consider the effects on performance and security when using dynamic sql.

ic3man7019 Over a year ago

@ahoxha Thank you. I agree about the dynamic SQL stuff. I do appreciate the answers, though. I'm sure it has its uses.

Sean Lange Over a year ago

This is a very common approach to this problem. It can suffer from performance thought because the execution plan will not always be consistent. Take a look at Gail Shaw's article on this type of query for another option using dynamic sql that is safe. sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

JNevill · Accepted Answer · 2017-04-20 17:16:26Z

0

You can either write a dynamic SQL statement and execute it using sp_ExecuteSQL in your procedure, or you can get tricky with the SQL like:

Select *
from dbo.test    
where (LessonName = @LessonName)
AND (StartDate = @DateFrom)
AND (EndDate = @DateTo) 
AND (LocationCode = @LocationCode or @LocationCode IS NULL or @LocationCode = '')

answered Apr 20, 2017 at 17:16

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Comments

Jorge Campos · Accepted Answer · 2017-04-20 17:19:01Z

0

You can use the COALESCE function to do so in this way:

where LessonName = coalesce(@LessonName, LessonName)
  AND StartDate = coalesce(@DateFrom, StartDate)
  AND EndDate = coalesce(@DateTo, EndDate) 
  AND LocationCode = coaleasce(@LocationCode, LocationCode)

Although I'm not sure about the empty strings. It will work for null values, in other databases coalesce also handle the empty strings. If it do not work you can use case in the same manner:

LessonName = case when @LessonName is not null and @LessonName != ''
                  then @LessonName else LessonName end

And just use the same logic for the other parameters.

answered Apr 20, 2017 at 17:19

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

Comments

McNets · Accepted Answer · 2017-04-20 17:22:43Z

0

INHO on this case a good way is using a dynamic query.

DECLARE @cmd VARCHAR(MAX);

SET @CMD = 'SELECT * FROM dbo.Text WHERE @Param1 = 'x''; --at least on parameter
IF @PARAM2 IS NOT NULL
BEGIN
    SET @CMD = @CMD + ' AND Param2 = @Param2'
END 
IF @PARAM3 IS NOT NULL
BEGIN
    SET @CMD = @CMD + ' AND Param3 = @Param3'
END 

EXECUTE (@CMD);

edited Apr 20, 2017 at 17:22

answered Apr 20, 2017 at 17:20

McNets

10.9k3 gold badges39 silver badges70 bronze badges

2 Comments

Jorge Campos Over a year ago

Not the best way for sure.

Sean Lange Over a year ago

Actually dynamic sql for this type of thing is pretty good from a performance perspective. But you need to parameterize your dynamic sql or it is vulnerable to sql injection.

Collectives™ on Stack Overflow

How do I include optional parameters using IF-THEN-ELSE logic in a SQL query?

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related