CODEWITHSUNDEEP
sqlsql-servervariableswhere-clause
HilJaclaIII
HilJaclaIII

Reputation: 89

SQL: Conditional where clause not working

UPDATE: We were advised that it is possible that values for both parameters will not be provided. In such case, there should be no filter for both mobile number and email address.

I'm having a hard time identifying what's wrong with my query. Basically, I have two variables

@EmlAdd
@MblNum

What I want to do is to search using the following rules: at least one of the two variables must have a value If I only supply value for email address , it must ignore the filter for mobile number If I only supply value for mobile number, it must ignore the filter for email address

What's happening with my solution so far is that: if values for 2 variables were supplied: IT WORKS if only one of the variables has value: DOESN'T WORK

DECLARE
@EmlAdd AS VARCHAR(100),
@MblNum AS VARCHAR(100)

SET @EmlAdd = ''
SET @MblNum = '5555555'

SELECT
    USER_ID,
CONTACT_VALUE
FROM
TBL_CONTACTS
WHERE
(
    (CONTACT_VALUE = @MblNum OR @MblNum = '')
        OR
    (CONTACT_VALUE = @EmlAdd OR @EmlAdd = '')
)

Any help will be greatly appreciated.

Upvotes: 0

Views: 3748

Answers (6)

Angelo
Angelo

Reputation: 335

DECLARE
@EmlAdd AS VARCHAR(100),
@MblNum AS VARCHAR(100)

SET @EmlAdd = ''
SET @MblNum = '5555555'

SELECT USER_ID, CONTACT_VALUE
FROM
TBL_CONTACTS
WHERE
(
  (CONTACT_VALUE = case when @MblNum = '' then @EmlAdd else case when @EmlAdd = '' then @MblNum  end end)

)

Upvotes: 0

Steveo
Steveo

Reputation: 13

Remove the OR @MblNum = '' and OR @EmlAdd = ''

When only one parameter has a value set the other will equal '' (0 length string) and therefore the logical condition will be met on every call. 

DECLARE
@EmlAdd AS VARCHAR(100),
@MblNum AS VARCHAR(100)

SET @EmlAdd = ''
SET @MblNum = '5555555'

SELECT
    USER_ID,
CONTACT_VALUE
FROM
TBL_CONTACTS
WHERE
(
    (CONTACT_VALUE = @MblNum)
        OR
    (CONTACT_VALUE = @EmlAdd)
        OR
    (@EmlAdd='' AND @MblNum='')
)

Upvotes: 0

gvee
gvee

Reputation: 17171

Because you're only searching a single data column you don't have to be so clever... unless you've neglected to provide other pertinent information.

DECLARE @EmlAdd As varchar(100)
      , @MblNum As varchar(100);

SET @EmlAdd = '';
SET @MblNum = '5555555';

/* Optional (recommended)
-- Set values to NULL if blank string is passed
SET @EmlAdd = NullIf(@EmlAdd, '');
SET @MblNum = NullIf(@MblNum, '');
*/

-- If both variables are NULL then no search criteria
IF Coalesce(@EmlAdd, @MblNum) IS NULL THEN
  BEGIN
    SELECT USER_ID
         , CONTACT_VALUE
    FROM   TBL_CONTACTS;
  END
-- At least one variable is not NULL so let's look for it
ELSE
  BEGIN
    SELECT USER_ID
         , CONTACT_VALUE
    FROM   TBL_CONTACTS
    WHERE  CONTACT_VALUE IN (@EmlAdd, @MblNum);
  END
;

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

What you are saying is if MblNum is blank OR if EmlAdd is blank return everything.

You may want to change the middle OR to an AND.

WHERE
(
    (CONTACT_VALUE = @MblNum OR @MblNum = '')
        AND
    (CONTACT_VALUE = @EmlAdd OR @EmlAdd = '')
)

that way the MblNum must match the CONTACT_VALUE AND the EmlAdd must match or be empty and visa versa. Now if you include both paramters then they both must match for the and. If you still want either one to match then drop the check for an empty parameter,

WHERE CONTACT_VALUE = @MblNum OR CONTACT_VALUE = @EmlAdd

If either parameter is empty it shouldn't match CONTACT_VALUE and will be ignored.

Upvotes: 0

Devart
Devart

Reputation: 122002

Try this one -

DECLARE 
      @EmlAdd VARCHAR(100)
    , @MblNum VARCHAR(100)

SELECT 
      @EmlAdd = ''
    , @MblNum = '5555555'

SELECT [USER_ID], CONTACT_VALUE
FROM dbo.TBL_CONTACTS
WHERE CONTACT_VALUE IN (@MblNum, @EmlAdd)

Upvotes: 1

Low Chee Mun
Low Chee Mun

Reputation: 610

u are almost there, replace '' to is null

DECLARE @SAMPLE TABLE
(
[USER_ID] INT IDENTITY(1,1),
[CONTACT_VALUE] NVARCHAR(255)
)

INSERT INTO @SAMPLE 
VALUES 
('5555555'),
('[email protected]')

DECLARE
@EmlAdd AS VARCHAR(100),
@MblNum AS VARCHAR(100)

SET @EmlAdd = ''
SET @MblNum = '5555555'

SELECT
USER_ID,
CONTACT_VALUE
FROM
@SAMPLE
WHERE

--the changes is here, replace '' to is null, as this is standard query practise , cheer =)

(@EmlAdd is null or CONTACT_VALUE =@EmlAdd)
or
(@MblNum is null or CONTACT_VALUE = @MblNum)

Upvotes: 0

Related Questions