Click here to Skip to main content
13,199,026 members (49,582 online)
Rate this:
 
Please Sign up or sign in to vote.
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:
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
Jamie8881.7K
Updated 22-Dec-16 18:26pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

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)
  Permalink  
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hello,

try this :

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
  Permalink  
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.171020.1 | Last Updated 23 Dec 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100