How to use if else using "AND" / "OR operator in SQL

Question

4.00/5 (1 vote)

See more:

I dont want to use if else for very small purpose which took me to write code again and again in the blocks.
How to write it using AND / OR operator:

SQL

if (@datafor year=0)
select * from abc
else
select * from employee
where dataforyear=@dataforyear

Posted 2-Sep-14 2:00am

Torakami

Updated 2-Sep-14 2:35am

Leo Chapiro

v3

Add a Solution

3 solutions

Solution 1

I think you have mentioned two different table names in those two SELECT query. I prefer something like this. Check that article for few more examples.

SQL

DECLARE @dataforyear AS NUMERIC
DECLARE @SQLQuery AS NVARCHAR(500)

SET @dataforyear = 0 
SET @SQLQuery = 'SELECT * FROM employee '

IF (@dataforyear <> 0)
BEGIN
   @SQLQuery = @SQLQuery + ' WHERE dataforyear = ' + @dataforyear
END

EXECUTE sp_executesql @SQLQuery

Building Dynamic SQL In a Stored Procedure[^]

Posted 2-Sep-14 2:37am

thatraja

Solution 5

Your requirement is very simple and of course it has no need to use AND/OR also, if you know NULLIF, COALESCE functions in SQL. Your query can be modified as follows.

SQL

SELECT	*
FROM	Employee
WHERE	dataforyear = COALESCE(NULLIF(@dataforyear, 0), dataforyear)

Explanation:
If @dataforyear = 0 then NULLIF(@dataforyear, 0) will become NULL. Then COALESCE(NULLIF(@dataforyear, 0), dataforyear) will be COALESCE(NULL, dataforyear) gives you dataforyear in return.
If @dataforyear <> 0 then NULLIF(@dataforyear, 0) will become @dataforyear. Then COALESCE(NULLIF(@dataforyear, 0), dataforyear) will be COALESCE(@dataforyear, dataforyear) gives you @dataforyear in return.
Have fun with quries.

Posted 3-Sep-14 9:26am

DineshMaind

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)

_Asif_ · Accepted Answer · 2014-09-02T03:06:00

Solution 3

This should serve your purpose.

SQL

select * 
from employee
where (@dataforyear=0 OR dataforyear=@dataforyear)

Posted 2-Sep-14 3:06am

_Asif_

Comments

Torakami 2-Sep-14 9:14am

do i need to check for null as well ?

something like this
select *
from employee
where ((@dataforyear=0 or @DataForYear is null) OR dataforyear=@dataforyear))

_Asif_ 2-Sep-14 9:29am

well you can do this

select *
from employee
where (IsNull(@dataforyear,0) = 0 OR dataforyear=@dataforyear)

Torakami 3-Sep-14 0:47am

yeh , this is much better way .. thanks ...

Torakami 3-Sep-14 5:09am

Hey Sayed , What i think is here if @datafor year comes 0 it wont check in the condition isnull() .. which is wrong ..