1) IF ELSE STATEMENT IN SQL SERVER

IF (SELECT username FROM test )= 'senthil'

BEGIN

update test set username='sent' where uid=1

END

ELSE

insert into test values ('siva',2)

------------------------------------------------------------------------------------------------------------

2) MORE THAN ONE IF ELSE STATEMENT

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15

BEGIN

PRINT 'The following titles are excellent mod_cook books:'

PRINT ' '

SELECT SUBSTRING(title, 1, 35) AS Title

FROM titles

WHERE type = 'mod_cook'

END

ELSE

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15

BEGIN

PRINT 'The following titles are expensive mod_cook books:'

PRINT ' '

SELECT SUBSTRING(title, 1, 35) AS Title

FROM titles

WHERE type = 'mod_cook'

END

------------------------------------------------------------------------------------------------------------

3) CASE STATEMENT:

GO

SELECT ProductNumber, Name, 'Price Range' =

CASE

WHEN ListPrice = 0 THEN 'Mfg item - not for resale'

WHEN ListPrice < 50 THEN 'Under $50'

WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'

WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'

ELSE 'Over $1000'

END

FROM Product ORDER BY ProductNumber ;

GO

------------------------------------------------------------------------------------------------------------

4) Using CASE to replace the IIf function that is used in Microsoft Access

CASE provides functionality that is similar to the IIf function in Microsoft Access. The following example shows a simple query that uses IIf to provide an output value for the TelephoneInstructions column in an Access table that is named db1.ContactInfo.

SELECT FirstName, Lastname, TelephoneNumber,

IIf(IsNull(TelephoneInstructions),"Any time",

TelephoneInstructions) AS [When to Contact]

FROM db1.ContactInfo

------------------------------------------------------------------------------------------------------------

The following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks view, Person.vAdditionalContactInfo.

USE AdventureWorks

GO

SELECT FirstName, Lastname, TelephoneNumber, 'When to Contact' =

CASE

WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'

ELSE TelephoneSpecialInstructions

END

FROM Person.vAdditionalContactInfo

------------------------------------------------------------------------------------------------------------