CODEWITHSUNDEEP
sqlsql-servert-sql
R Loomas
R Loomas

Reputation: 333

How do I add conditional statements to test for a specific value in dynamic where clause in stored procedure

I have a stored procedure into which I pass a number of variables and I create a dynamic where clause using the "AND (@var IS NULL OR table.field = @var)" and it works great... EXCEPT

One of the variables (@statusLevel) can be either 1-9 or 10. When the value passed in is 10, I want to return ONLY those projects that have a statusLevel = 10. When the value passed in is between 1 & 9, I want to return all of the projects between that value (let's say '5') and less than 10.

I've got each part working perfectly independently but I'm lost on how to get them to work together.

AND ((@statusLevelID IS NULL OR project.statusLevelID >= @statusLevelID) AND (@statusLevelID IS NULL OR project.statusLevelID < 10))
AND (@statusLevelID IS NULL OR project.statusLevelID = 10)

Using an "OR" just gives me ALL of the projects.

AND ((@statusLevelID IS NULL OR project.statusLevelID >= @statusLevelID) AND (@statusLevelID IS NULL OR project.statusLevelID < 10)
OR (@statusLevelID IS NULL OR project.statusLevelID = 10))

I was thinking a CASE statement might work here but I'm not exactly sure how to implement that.

Any assistance is greatly appreciated. Thanks in advance.

Upvotes: 1

Views: 81

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272396

You can write the condition like so:

AND (
    @statusLevelID IS NULL
    OR
    project.statusLevelID BETWEEN @statusLevelID AND IIF(@statusLevelID = 10, 10, 9)
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You want one boolean expression connected by ORs:

AND ( (@statusLevelID IS NULL) OR
      (@statusLevelID = project.statusLevelID) OR
      (@statusLevelID <> 10 AND project.statusLevelID >= @statusLevelID AND project.statusLevelID < 10)
    )

Upvotes: 1

Related Questions