SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › Examples

Examples

Following is an example that uses a simple-when statement:

CASE creditStatus
   WHEN 'CURRENT' THEN
      call creditLimitCheck(clientId, creditAmount, creditLimit, orderStatus);
      IF (orderStatus = 'CREDIT LIMIT EXCEEDED')
         SET orderStatus = orderStatus CONCAT ', DISCUSS CREDIT LINE INCREASE';
      END IF;
   WHEN 'NEW APPLICATION' THEN
      set orderStatus = 'ACCEPT ORDER PENDING ACCOUNT SETUP COMPLETION';
   WHEN 'PAST DUE' THEN
      set orderStatus = 'NOTIFY CUSTOMER, HOLD FOR PAYMENT RECEIPT';
   WHEN 'BAD' THEN
      set orderStatus = 'REJECTED FOR FAILURE TO PAY';
END CASE;

Following is an example that uses a searched-when statement:

CASE
   WHEN creditStatus = 'CURRENT' OR clientStatus = 'PRE-APPROVED' THEN
      call creditLimitCheck(clientId, creditAmount, creditLimit, orderStatus);
      IF (orderStatus = 'CREDIT LIMIT EXCEEDED')
         SET orderStatus = orderStatus CONCAT ', DISCUSS CREDIT LINE INCREASE';
      END IF;
   WHEN creditStatus = 'NEW APPLICATION' THEN
      set orderStatus = 'ACCEPT ORDER PENDING ACCOUNT SETUP COMPLETION';
   WHEN creditStatus = 'PAST DUE' THEN
      set orderStatus = 'NOTIFY CUSTOMER, HOLD FOR PAYMENT RECEIPT';
   ELSE
      set orderStatus = 'CREDIT IS ' CONCAT orderStatus;
END CASE;