IF/ELSE Statement

Allows conditional execution of SQL statements.

Syntax

IF condition statement1 { ELSE statement2 }

Syntax Elements

condition
An SQL Search Condition.

statement1, statement2
A single SQL statement, or a group of statements enclosed in the BEGIN and END keywords.

Operation

statement1 is executed only if condition evaluates to true. If an ELSE clause is included and condition evaluates to false, statement2 is executed.

Example

CREATE PROC EX7(@TYPE INTEGER IN) RETURNS DECIMAL(6,2) AS

BEGIN

    DECLARE @V DECIMAL(6,2)

    IF @TYPE=1

        SET @V = (SELECT MIN(AMT) FROM TESTDATA)

    ELSE IF @TYPE=2

        SET @V = (SELECT MAX(AMT) FROM TESTDATA)

    ELSE

        SET @V = (SELECT AVG(AMT) FROM TESTDATA)

    RETURN @V

END

Creates a procedure called EX7 that returns the minimum, maximum or average value from the AMT column of the TESTDATA table, depending on the value of the @TYPE parameter.