SQL CASE Examples

-- --------------------------------------------------

-- Simple CASE function:

-- --------------------------------------------------

CASE input_expression

      WHEN when_expression THEN result_expression

      ELSE else_result_expression

END

-- --------------------------------------------------

-- Searched CASE function:

-- --------------------------------------------------

CASE

      WHEN Boolean_expression THEN result_expression

      ELSE else_result_expression

END

-- CASE with multiple values tested

CASE

      WHEN t1.Column1 IN(103,109,83) THEN 'PassedTest'

      ELSE NULL

END

-- --------------------------------------------------

-- SELECT calculation

-- --------------------------------------------------

SELECT

(2 *

      CASE @RequestedSchedule

            WHEN 'M' THEN @intMonthly

            WHEN 'Q' THEN @intQuarterly

            ELSE 'Unexpected Value in RequestedSchedule: ' + @RequestedSchedule

      END

)

-- --------------------------------------------------

-- SELECT CASE EXISTS

-- --------------------------------------------------

SELECT

      a.Column01

      ,a.Column02

      ,CASE WHEN EXISTS (SELECT z.Column05 FROM TableZ AS z WHERE zColumn06 = a.Column01)

            THEN 'Exists'

            ELSE 'Not Exists'

      END

FROM

      TableA AS a

-- --------------------------------------------------

-- Set selection mode based upon passed parameters

-- --------------------------------------------------

DECLARE

      @currentSelectionMode int

      ,@selectionModeByAAA int

      ,@selectionModeByBBB int

SELECT

      @selectionModeByAAA = 1

      ,@selectionModeByBBB = 2

SELECT @currentSelectionMode =

      CASE

            WHEN @passedValue01 IS NOT NULL THEN @selectionModeByAAA

            WHEN @passedValue02 IS NOT NULL THEN @selectionModeByBBB

            ELSE 0

      END

IF (@currentSelectionMode = 0)

BEGIN

      RAISERROR('No valid selection criteria was passed.', 11, 1)

      -- Ths first number, severity, is the user-defined severity level associated with this message.

      -- Severity levels from 0 through 18 can be specified by any user.

      -- NOTE: Must be >= 11 in order to throw an error in C#

      RETURN -1

END