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