if statements in SQL Query

[basha khadar]

hi,
how to use IF STATEMENTS in SQL Query?

[iburyak]

Syntax

[PHP]IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ] [/PHP]


Examples
A. Use one IF...ELSE block
This example shows an IF condition with a statement block. If the average price of the title is not less than $15, it prints the text: Average title price is more than $15.

[PHP]USE pubs

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
PRINT 'Average title price is more than $15.'[/PHP]

Here is the result set:

The following titles are excellent mod_cook books:

Title
-----------------------------------
Silicon Valley Gastronomic Treats
The Gourmet Microwave

(2 row(s) affected)

B. Use more than one IF...ELSE block
This example uses two IF blocks. If the average price of the title is not less than $15, it prints the text: Average title price is more than $15. If the average price of modern cookbooks is more than $15, the statement that the modern cookbooks are expensive is printed.

[PHP]USE pubs

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[/PHP]

This is from Help. I can just add that first part of IF statement is when condition is True and second when it is False.

BEGIN
.....
END

Used when you have more then one line of code after IF or Else.


Good Luck.