CODEWITHSUNDEEP
sqlsql-server
ic3man7019
ic3man7019

Reputation: 711

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

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.

Upvotes: 4

Views: 2138

Answers (4)

McNets
McNets

Reputation: 10807

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

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23361

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.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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)

Upvotes: 4

JNevill
JNevill

Reputation: 50019

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 = '')

Upvotes: 0

Related Questions