Ignore Parameter if it is NULL Value SQL Server 2008 query

Hello Experts,

I am trying to write a SQL Query (SQL Server 2008) using three parameters. I would like the Query to run and only sort for the parameters that are not a NULL value.

For example - I want the query to act like this:

1. All Parameters have a value (This works fine)

@Param1 int = 1,
@Param2 int = 6,
@Parm3 varchar(5) = ‘Last’;

Select A_Column, B_Column, C_Column From My_Table
Where A_Column Between @Param1 and @Param2 And C_Column = @Param3

 Output:
1 2012 Last
2 2012 Last
6 2012 Last

(This works fine)

2. Now I want to have the query ignore a parameter if it has a Null value like this: E.g. @Param3 = ‘ ’ (is NULL)

@Param1 int = 1,
@Param2 int = 6,
@Parm3 varchar(5) = ‘ ’;

Select A_Column, B_Column, C_Column From My_Table
Where A_Column Between @Param1 and @Param2 And C_Column = @Param3

Output:
1 2012 Last
2 2012 Last
3 2012 First
4 2012 First
5 2012 First
6 2012 Last

Does anyone know the correct syntax to ignore a parameter if it is NULL?

Thanks!
SaxitalisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
'' is an empty string but it is not NULL, see: https://en.wikipedia.org/wiki/Empty_string

if you are setting the parameter to '' you would do this:
@Param1 int = 1,
@Param2 int = 6,
@Parm3 varchar(5) = ''

SELECT
      A_Column, B_Column, C_Column
FROM My_Table
WHERE A_Column BETWEEN @Param1 AND @Param2
      AND (
           (C_Column = @Param3 AND @Param3 <> '')
        OR 
            @Param3 = ''
          )

Open in new window


or if using NULL
@Param1 int = 1,
@Param2 int = 6,
@Parm3 varchar(5) = NULL

SELECT
      A_Column, B_Column, C_Column
FROM My_Table
WHERE A_Column BETWEEN @Param1 AND @Param2
      AND (
           (C_Column = @Param3 AND @Param3 IS NOT NULL)
        OR 
            @Param3 IS NULL
          )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The additional check is not necessary, but emphasizes the meaning. Technically, this conditions are sufficient (using same code):
@Param1 int = 1,
@Param2 int = 6,
@Parm3 varchar(5) = ''

SELECT
      A_Column, B_Column, C_Column
FROM My_Table
WHERE A_Column BETWEEN @Param1 AND @Param2
      AND (C_Column = @Param3 OR @Param3 = '')

Open in new window

@Param1 int = 1,
@Param2 int = 6,
@Parm3 varchar(5) = NULL

SELECT
      A_Column, B_Column, C_Column
FROM My_Table
WHERE A_Column BETWEEN @Param1 AND @Param2
      AND (C_Column = @Param3 OR @Param3 IS NULL)

Open in new window

HuaMin ChenProblem resolverCommented:
Try
Select A_Column, B_Column, C_Column From My_Table
Where A_Column Between @Param1 and @Param2 And C_Column = isnull(@Param3,C_Column)

Open in new window

SaxitalisAuthor Commented:
thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.