SQL Server : Control-Of-Flow Language - T-SQL Programming

BEGIN & END Block :

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 and END are not used, only the first statement that immediately follows IF...ELSE or WHILE is executed.

The command Syntax is :

 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 :

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

IF..ELSE constructs can be used in batches, in stored procedures and in ad hoc queries. IF..ELSE constructs can be nested. There is no limit to the number of nesting levels.

Consider the following examples :

 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 IF statements. The CASE statement enables multiple possible conditions to tbe managed within a SELECT statement.

The Syntax is :

 CASE 
    WHEN Boolean_expression THEN expression1 [[WHEN Boolean_expression THEN expression][...]
    [ELSE expression]]
 END

Example :

 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'
    END
 FROM titles
 WHERE 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. SQL Server provides BREAL and CONTINUE statements to control the loop from inside of the WHILE construct.

The command Syntax is :

 WHILE Boolean_expression
     { sql_statement | statement_block }
   [BREAL]
     { sql_statement | statement_block }
   [CONTINUE]

Example :

 WHILE (SELECT AVG(price) FROM titles) < 60
  BEGIN
    SELECT title FROM titles
    IF (SELECT MAX(price) FROM titles) > 20
         BREAK
    ELSE
         CONTINUE
 END