Example: Searched CASE - Advanced SQL Engine - Teradata Database

The following stored procedure includes a searched CASE statement.

CREATE PROCEDURE spSample (IN pANo INTEGER,
                   IN pName CHARACTER(30), OUT pStatus CHARACTER(50))
BEGIN
  DECLARE vNoAccts INTEGER DEFAULT 0;
   SELECT COUNT(*) INTO vNoAccts FROM Accounts;
   CASE
      WHEN vNoAccts = 0 THEN
          INSERT INTO Accounts (pANo, pName);
      WHEN vNoAccts = 1 THEN
          UPDATE Accounts
            SET aName = pName WHERE aNo = pANo;
      WHEN vNoAccts > 1 THEN
            SET pStatus = 'Total ' || vNoAccts || ' customer                                            accounts';
   END CASE;
END;

In the preceding example, the appropriate SET statement of a WHEN clause is executed depending on the value of the local variable vNoAccts.

INSERT INTO Accounts (pANo, pName);

UPDATE Accounts 
   SET aName = pName 
    WHERE aNo = pANo;

SET pStatus = ’Total’ ||
vNoAccts || ’customer accounts’;

If the value of vNoAccts is NULL, the stored procedure raises a runtime exception (“Case not found for CASE statement”, SQLSTATE=’20000’, SQLCODE = 7601) in the absence of the ELSE clause. However, vNoAccts cannot be set to NULL by this example.