Example: Simple CASE - Advanced SQL Engine - Teradata Database

The following stored procedure includes a simple CASE statement.

CREATE PROCEDURE spSample(IN  pANo    INTEGER,
                          IN  pName   CHARACTER(30),
                          OUT pStatus CHARACTER(50))
BEGIN
  DECLARE vNoOfAccts INTEGER DEFAULT 0;
   SELECT COUNT(*) INTO vNoOfAccts FROM Accounts;
     CASE vNoOfAccts
        WHEN 0 THEN
            INSERT INTO Accounts (pANo, pName);
        WHEN 1 THEN
            UPDATE Accounts
            SET aName = pName WHERE aNo = pANo;
        ELSE
            SET pStatus = 'Total ' || vNoOfAccts || '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 vNoAccts.

INSERT INTO Accounts (pANo,               pName);

UPDATE Accounts 
    SET aName = pName 
   WHERE aNo = pANo;

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