When series of statements need to be executed it is better to enclose them in blocks.SQL server provides the BEGIN...END block for this purpose and the statements enclosed between BEGIN and END block are known as statement block. Statement blocks are used with IF..ELSE and WHILE control-of-flow language. If BEGIN andEND are not used, only the first statement that immediately follows IF...ELSE orWHILE is executed.
The command Syntax is :
The command Syntax is :
1
2
3
| BEGIN { sql_statement | statement_block }END |
IF & ELSE Block :
Statements to be executed conditionally are identified with the IF..ELSE construct. The IF..ELSE block allows a statement or statement block to be executed when a condition is TRUE or FALSE.
The command Syntax is :
The command Syntax is :
1
2
3
4
5
| IF Boolean_expression { sql_statement | statement_block }[ ELSE Boolean_expression { sql_statement | statement_block } ] |
Consider the following examples :
1
2
3
4
5
6
7
| USE pubs IF (SELECT SUM(price) FROM titles WHERE type='business') < 600 BEGIN PRINT ' The sum of the following BUSINESS books is less than 600 Dollars :' PRINT '' SELECT * FROM titles WHERE type='business' END |
CASE CONSTRUCT :
SQL Server provides a CASE statement where you need a large number of IFstatements. The CASE statement enables multiple possible conditions to tbe managed within a SELECT statement.
The Syntax is :
The Syntax is :
1
2
3
4
| CASE WHEN Boolean_expression THEN expression1 [[WHEN Boolean_expression THEN expression][...] [ELSE expression]]END |
1
2
3
4
5
6
7
8
9
10
| SELECT CASE WHEN type='BUSINESS' THEN 'BUSINESS BOOK' WHEN type='mod_cook' THEN 'MODERN COOKING' WHEN type='trad_cook' THEN 'TRADITIONAL COOKING' WHEN type='psychology' THEN 'PSYCHOLOGY BOOK' ELSE 'No Category assigned as yet' ENDFROM titlesWHERE title_id, LIKE 'bu%' OR title_id LIKE 'MC%' OR title_id LIKE 'PC%' OR title_id LIKE 'PS%' group by type |
WHILE CONSTRUCT :
The WHILE construct is used for the repeated execution of SQL Statements. The statements are executed repeatedly as long as the specified condition is true. SQLServer provides BREAL and CONTINUE statements to control the loop from inside of the WHILE construct.
The command Syntax is :
The command Syntax is :
1
2
3
4
5
| WHILE Boolean_expression { sql_statement | statement_block } [BREAL] { sql_statement | statement_block } [CONTINUE] |
1
2
3
4
5
6
7
8
| WHILE (SELECT AVG(price) FROM titles) < 60 BEGIN SELECT title FROM titles IF (SELECT MAX(price) FROM titles) > 20 BREAK ELSE CONTINUEEND |
