Presents your 
SQL SERVER E-NEWSLETTER for January 16, 2003

<------------------------------------------->

MASTER THE CONTROL-OF-FLOW T-SQL KEYWORDS

The control-of-flow keywords are used in T-SQL to control the logic
sequence. If you are familiar with flowcharts, these control-of-flow 
keywords implement the flowchart's logic structure.

Simple pseudocode written with logical structures can be translated 
into T-SQL. For instance, the following simple pseudocode:

WHEN TYPE OF SALE IS PENDING PUT ORDER IN PENDING QUEUE
OTHERWISE WHEN TYPE OF SALE IS COMPLETE PUT IN COMPLETED STACK
IF TYPE OF SALE CANNOT BE DETERMINED PUT IN UNKNOWN UNRESOLVED QUEUE

can be translated into the following incomplete T-SQL statements:

IF @TypeOfSale = 1 -- PENDING
BEGIN
     INSERT PendingOrder . . ..
END
ELSE
IF @TypeOfSale = 2 -- Complete
BEGIN
     INSERT CompleteOrder. . .
END
ELSE
BEGIN
    INSERT UnresolvedOrder. . .
END

Here is a list of the control-of-flow T-SQL keywords:

* BEGIN. . .END
* BREAK
* CONTINUE
* GOTO
* IF...ELSE
* RETURN
* WAITFOR
* WHILE

The control-of-flow keywords are different from regular keywords
because:

* They act as gateways to or from a set of T-SQL statements to carry 
out known actions as a result.

* They may change the flow of logic.

You may believe that the CASE T-SQL keyword would be a part of the
control-of-flow keyword set, but it's not. The CASE statement will not 
change the flow of the logic, but it may assist in evaluating conditions that
the control-of-flow statements will use to determine the flow of logic.

The CASE statement can be used to create dynamic and executable T-SQL.
Therefore, it can be converted to be used as a control-of-flow T-SQL
statement.

For instance, the IF. . .ELSE scenario could be translated using a CASE
statement as the following incomplete T-SQL statements show:

SELECT @cmd =
CASE @TypeOfSale
WHEN 1 THEN 'EXEC PendingOrder @OrderId = '
WHEN 2 THEN 'EXEC CompleteOrder @OrderId = '
ELSE 'EXEC UnresolvedOrder @OrderId = '
END
SELECT @cmd = @cmd + '''' + @CnvOrderId + ''''
EXEC (@cmd)
----------------------------------------