How to enable optional parameters using IF-THEN-ELSE logic in SQL query?

Question

Here is the create statement for the 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 for a database based on parameter values. For instance:

Select *
from dbo.test

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

Pretty simple, right? However, if any of these parameters is null (or contains an empty string), I would like to omit them from the search and search only for parameters that are not equal to zero. I thought 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.

Answer 1

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

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)

Answer 2

SQL, sp_ExecuteSQL , SQL:

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

Answer 3

You can use COALESCE to do this as follows:

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 lines. It will work for null values, and in other databases an empty string will also be executed. If this does not work, you can use casein the same way:

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

And just use the same logic for other parameters.

Answer 4

INHO in 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);