# Flow Control

There are diferent flow control keywords used by SQL

TAGS: Computers, Databases, Programming, SQL, Tech

## IF

When the condition is true, execute the next line, otherwise skip it.

IF BooleanExpression
{statement | block} -- Do if BooleanExpression=TRUE. Continue at 2nd comment.
-- Always do this line.


## IF...ELSE

When the condition is true, execute the next line and then jump to the second line after the ELSE statement. When the condition is false, jump to the line after the ELSE statement.

IF BooleanExpression
{statement | block} -- Do only if BooleanExpression=TRUE. Continue at 3rd comment.
ELSE
{statement | block}  -- Do only if BooleanExpression=FALSE. Continue at 3rd comment.
-- Always do this line.


IF...ELSE statements can be chained together.

IF BooleanExpression
{statement | block}
ELSE IF BooleanExpression
{statement | block}
ELSE IF BooleanExpression
{statement | block}
ELSE
{statement | block}
-- Always do this line.


IF...ELSE statement can be nested.

IF BooleanExpression
BEGIN
IF BooleanExpression
{statement | block}
ELSE
{statement | block}
END
ELSE
{statement | block}
-- Always do this line.


## BEGIN...END

Demarcates a block of Transact-SQL statements that can act as a single line. EG:

IF @Variable <> 0
BEGIN
-- Multiple statements go here
END


## CASE

Note that CASE is different from the other flow control keywords in that CASE is actually used in SELECT statements as opposed to controlling when other statements will execute.

Returns a given expression when given another.

CASE InputExpression
WHEN WhenExpression THEN ResultExpression
[...]
[ ELSE ElseResultExpression ]
END

CASE
WHEN BooleanExpression THEN ResultExpression
[...]
[ ELSE ElseResultExpression ]
END


CASE is often used as an on-the-fly look up table. EG:

SELECT
CASE col1
WHEN 'x' THEN 'xxx'
WHEN 'y' THEN 'yyy'
ELSE 'aaa'
END AS 'Column 1 Lookup',
'Column 2 Lookup' =
CASE
WHEN SUBSTRING(tbl1.col2,1,2) = 'xx' THEN 'x'
WHEN SUBSTRING(tbl1.col2,1,2) = 'yy' THEN 'y'
ELSE 'a'
END
FROM tbl1


Here is a friendlier example:

SELECT
Name,
Party = CASE Name
WHEN 'R' THEN 'Republican'
WHEN 'D' THEN 'Democrat'
ELSE 'Other'
END,
AgeGroup = CASE
WHEN Age <  12  THEN 'Child'
WHEN Age >= 12 AND < 20 THEN 'Teen'
WHEN Age >=20 AND < 65 THEN 'Adult'
WHEN Age >=65 THEN 'Senior'
END
FROM Voter


## GO

The GO command is not part of SQL but is used by SQL Server to indicate the end of a batch, i.e. a set of statements submitted for execution as a group. In contrast, a semicolon marks the end of a statement. The GO command also destroys any variables after it is run.

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON
GO

-- The above GO command is needed because CREATE PROCEDURE must be the first statement of its batch

CREATE PROCEDURE MyStoredProcedure
AS
BEGIN
...
END


## GOTO

Causes execution to continue at a specified label. EG:

IF @Variable = 3
GOTO hotel
-- Misc. statements herehotel:
-- Execution would continue here when the IF statement is FALSE


## RETURN

Terminates execution.

## TRY ... CATCH

Statements in the TRY block are run in order, but if an error (with severity 11+) occurs then control is passed to the CATCH block. This is not the same as a TRANSACTION. For more see Error Handling.

BEGIN TRY
SELECT 1 AS A;   -- Runs.
SELECT 2/0 As B; -- Errs.
SELECT 3 AS C;   -- Never runs.
END TRY
BEGIN CATCH
SELECT 4 AS D;   -- Runs.
END CATCH


## WAITFOR DELAY

Specifies a delay period (up to 24 hours) before continuing.

## WAITFOR TIME

Specifies a time to continue.

## WHILE

WHILE BooleanExpression
{SQLstatement | SQLStatementBlock}
NextLine


If the BooleanExpression is true, then go on to the next line (or block), and repeat it while the BooleanExpression is true with the following exceptions:

• If the BREAK keyword is encountered inside of the SQLStatementBlock, then exit the innermost loop and continue at the NextLine .
• If the CONTINUE keyword is encountered inside of the SQLStatementBlock, then go immediately to WHILE BooleanExpression .

GeorgeHernandez.comSome rights reserved