Thursday, June 21, 2007

SQL IF...ELSE Statement

SQL IF...ELSE Statement used to test a condition. IF...ELSE Statement using in execution of a Transact-SQL statement (Store Procedure or T-SQL) and Trigger.
IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.


IF condition is satisfied and the Boolean expression returns TRUE, it will executed IF Block Sql statement.
IF condtion is not satisfied and the Boolean expression returns FALSE, it will executed ELSE Block Sql Statement query.

Syntax for IF...ELSE
IF ( Boolean_expression )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
Sql Statement Block
END


Below is simple example of IF...ELSE Statement With 1 IF...ELSE Block
For Boolean_expression part, you can replace with your condition to match with your Sql query.
It also can using Exists in the Condition to check the existence of a Sql Statement or Row.
IF (3>2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Correct

IF (3<2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END

Value = Wrong

Below is another Example of More than 1 IF...ELSE Sql Statement Block
IF (10<2)
   SELECT 'Correct'
ELSE
   SELECT 'Wrong'
   IF ((10-2) > 5)
      SELECT 'More Than 5'
Value = Wrong
Value = More Than 5

If your IF...ELSE Block Sql Statement only have 1 Sql query, you no need to include the BEGIN...END. If your IF...ELSE Block Sql Statement have more than 1 Sql query, you need to include the BEGIN...END to let it executed all the Sql Query in your Sql Query Block.

14 comments:

Ankur Gupta said...

good job dude!

Viren said...

Nice website designing using Blogspot,
Can u help me to create such kind of blogs having Hyperlinks?
Thank you.

Ashley said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword. I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

INDRASENA REDDY said...

DECLARE @startDate DATETIME,
@endDate DATETIME;
SET @startDate='2009.04.15';
SET @endDate='2009.04.30';
DECLARE @wholepart FLOAT;
DECLARE @fraction FLOAT;
IF(MONTH(@startdate)=MONTH(@enddate))
BEGIN
IF (MONTH(@endDate)IN(1,3,5,7,8,10,12))
BEGIN
SELECT @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction =DATEDIFF(DAY,@startDate,DATEADD(DAY,1,@endDate)) *1.0/31.0
SELECT ROUND(@wholepart+@fraction,0)
END
IF (MONTH(@endDate)IN(4,6,9,11)) AND (DAY(@startdate)>15)
BEGIN
SELECT @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction =DATEDIFF(DAY,@startDate,DATEADD(DAY,1,@endDate)) *1.0/30.0
SELECT ROUND(@wholepart+@fraction,0)

END
IF(MONTH(@startdate)=2) AND (DAY(@startdate)>14)
IF(YEAR(@startdate)%4=0)
SELECT @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction = DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),DATEADD(DAY,1,@endDate)) *1.0/29.0
ELSE
SELECT @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction = DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),DATEADD(DAY,1,@endDate)) *1.0/28.0
SELECT ROUND(@wholepart+@fraction,0)
END
ELSE
SELECT @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction =DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),@endDate) *1.0/
CASE WHEN MONTH(@endDate) IN (1,3,5,7,8,10,12) THEN 31.0
WHEN MONTH(@endDate) IN (4,6,9,11) THEN 30.0
WHEN MONTH(@endDate)=2 AND (YEAR(@endDate)%4 = 0) THEN 29.0
ELSE 28.0 END

SELECT @wholepart+@fraction
SELECT round(@wholepart+@fraction,0)

i am getting null values can u correct it

Mind said...

buy Aion Kinah

Nice post

Mind said...

wow gold kaufen


Excellent post

uday said...

hello i am uday satardekar from ghotgewadi ,dodamarg.
and i select email from table but if depends on 4 combo box.if one null then omitt it.how?

Sachin Jain said...

Good Job!!!

-悦音- said...
This comment has been removed by the author.
Newbie said...

Great ... for another sql server issue, please visit http://sqltosql.blogspot.com/search/label/sql if else if

Memtech said...
This comment has been removed by the author.
Memtech said...

This is very informative article. Thanks for sharing with us. Following links also helped me.

http://www.mindstick.com/Blog/354/Checking%20IF%20ELSE%20condition%20in%20Stored%20Procedure%20SQL%20Server

http://msdn.microsoft.com/en-us/library/ms182717(v=sql.90).aspx

Bhaskar Reddy said...

hi Indrasena Reddy Garu...,
am Bhaskar Reddy plz send me ur contact details(like Nor and gmail id)i have some doubts in SQL, PL/SQL topics i cotact with U.. please share with me. This is My gmil id is yaravabhaskarreddy@gmail.com

n narendran said...

Good Example,
Thanks

DECLARE @Number int;
SET @Number = 50;
IF @Number > 100
PRINT 'The number is large.';
ELSE
BEGIN
IF @Number < 10
PRINT 'The number is small.';
ELSE
PRINT 'The number is medium.';
END ;
GO

Narendran
http://n4narendran.blogspot.in/