Avatar of SamCash
SamCash
Flag for United States of America asked on

MS SQL Conditional WHERE clause

If a parameter is not null, then include it in the WHERE clause.  I am trying to avoid a complex IF or CASE.  There are 8 @parameters, my example for simplicity has only 3.  In English, if a @paramX is null do not include it in the WHERE clause.

The following doesn't work (you can't have the "AND" in the IsNull function.  I am just trying to describe the goal.
SELECT *
FROM TableX
WHERE
   IsNull(@param1, AND @param1 = col1)
   IsNull(@param2, AND @param2 = col2)
   IsNull(@param3, AND @param3 = col3)

The following doesn't work either (there is no INCLUDE used this way.  I am just trying to describe the goal.
SELECT *
FROM TableX
WHERE
   IF @param1 IS NOT NULL INCLUDE "AND @param1 = col1"
   IF @param2 IS NOT NULL INCLUDE "AND @param2 = col2"
   IF @param3 IS NOT NULL INCLUDE "AND @param1 = col3"

I believe a complicated IF statements can solve this but with 8 @parameters I would have 256 IF statements with the correct WHERE clause in each one.  There must be an easier and simpler way to do this.  Easier to code, debug, test, and maintain.

Kind Regard
Sam
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
SamCash

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mike Eghtebas

WHERE
@param1 = ISNULL(@param1, col1) AND
@param2 = ISNULL(@param2, col2) AND 
@param3 = ISNULL(@param3, col3) 

Open in new window


explanation:
When @param1 at line 2 is not null it is set to itself (@param1, meaning it rigors it). Otherwise it is set equal to col1.

edited...
SamCash

ASKER
Scott,

Excellent, works perfectly!

Regards
Sam
Your help has saved me hundreds of hours of internet surfing.
fblack61