How to use other parameter value if the first is empty

Question

0.00/5 (No votes)

See more:

Hi, I have a SQL query in which I will pass either first or second parameter. If first is empty/null, then I will use second parameter value in my WHERE clause. But I have one confusion, how can I write my query in my WHERE clause to cater my case here?
Below are my script:

SQL

DECLARE @p_CustomerName AS VARCHAR(20)
DECLARE @p_CustomerID AS VARCHAR(20)

SET @p_CustomerName = NULL
SET @p_CustomerID = '123ABC'

SELECT
 FirstName
,LastName
FROM
CustomerTable AS CT
WHERE
--Doubts here
--CustomerName and CustomerID consists in 2 different columns, therefore OR clause is not suitable here

What I have tried:

I know I can use IF ELSE statement here, but there would be 2 queries. I am trying to achieve it in a single query.

Posted 22-Dec-16 16:03pm

Jamie888

Updated 22-Dec-16 18:26pm

v2

Add a Solution

2 solutions

Solution 2

Hello,

try this :

C#

SELECT FirstName, LastName FROM CustomerTable AS CT
WHERE
CustomerName = CASE WHEN ISNULL(@p_CustomerName, '') = '' THEN CustomerName ELSE ISNULL(@p_CustomerName, '') END
AND CustomerID = CASE WHEN ISNULL(@p_CustomerID, '') = '' THEN CustomerID ELSE ISNULL(@p_CustomerID, '') END

Posted 22-Dec-16 18:26pm

KDGOLE

Updated 22-Dec-16 18:30pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2016-12-22T18:24:00

Solution 1

Try this:

SELECT FirstName, LastName FROM customertable WHERE
(CustomerName=@p_CustomerName OR @p_CustomerName IS NULL) AND
(CustomerID=@p_CustomerID OR @p_CustomerID IS NULL)

Posted 22-Dec-16 18:24pm

Peter Leow

Updated 22-Dec-16 18:25pm

v2

Comments

Jamie888 23-Dec-16 3:02am

Sir, thank you for your reply. It has worked as expected although with some performance issue. Anyway, thank you for your help. Really appreciate it.