Treat parameter as an empty string or null if user did not enter any value

Question

I have a simple stored procedure that looks something like this:

SET @email = NULL

SELECT * FROM empTable
WHERE email = ISNULL(@email, '')

@email will take input from the user. The problem is, the email column in the table can be an empty string or null (or an email).

If the user doesn't enter any value, it should return all Employees with either NULL or empty string email.

I have tried to following but it didn't work too:

SELECT * 
FROM empTable
WHERE email = ISNULL(@email, '') OR email = ISNULL(@email, NULL)

Tab Alleman · Accepted Answer · 2014-11-10 18:31:20Z

1

SELECT * FROM empTable
WHERE email = ISNULL(@email, '') OR (email IS NULL AND @email IS NULL)

Or:

SELECT * FROM empTable
WHERE ISNULL(email,'') = ISNULL(@email, '')

edited Nov 10, 2014 at 18:31

answered Nov 10, 2014 at 18:20

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dgan · Accepted Answer · 2014-11-10 18:35:21Z

0

if email is then condition after second OR will be considered

if email is present then first curly brackets will be considered

SELECT * FROM empTable
WHERE (email=@email) or (@email is null OR email = ISNULL(@email, ''))

edited Nov 10, 2014 at 18:35

answered Nov 10, 2014 at 18:16

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

3 Comments

C.J. Over a year ago

It doesn't work, now if the input is NULL, it will just return EVERYTHING

Dgan Over a year ago

If the user doesn't enter any value, it should return all Employees

C.J. Over a year ago

it should return "all Employees with either NULL or empty string email"

marc_s · Accepted Answer · 2014-11-10 19:10:41Z

0

Try like this:

IF @email has value then corresponding row will retrieve from DB; else second condition will execute which will retrieve email with NULL and empty string.

SELECT * 
FROM empTable
WHERE email = ISNULL(@email, '') OR (email IS NULL and email = '')

edited Nov 10, 2014 at 19:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 10, 2014 at 18:20

knkarthick24

3,24619 silver badges21 bronze badges

1 Comment

knkarthick24 Over a year ago

Thanks for upvote+downvote. Hope you got the output.

Collectives™ on Stack Overflow

Treat parameter as an empty string or null if user did not enter any value

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related