Help! I Need An Assertion!

Not an engine assertion, not like "I've got an assertion! What should I do?", but one of my own...

---

Question: How do I force an exception when my SQL code hits an "impossible" situation?

In other words, how do I code an assertion test inside my stored procedure or trigger?

---

Answer: Pick a number, any number (as long as it's somewhere between 99000 to 99999, let's say 99002) and then use DECLARE EXCEPTION and SIGNAL:

IF [pigs were seen aloft] THEN
   BEGIN
      DECLARE @exception EXCEPTION FOR SQLSTATE '99002';
      SIGNAL @exception;
   END;
END IF;

That's all there is to it: A single chunk of code inserted into your procedure:

• No need to DECLARE the exception somewhere else,
  that's what the local BEGIN block is for.
  

• You can even use the same name "@exception" over and over again in the same procedure,
  [repeat refrain]
  that's what the local BEGIN block is for.
  

Here's a test:

CREATE PROCEDURE p()
BEGIN

   DECLARE @x INTEGER;

   SET @x = NULL;

   IF @x IS NULL THEN
      BEGIN
         DECLARE @exception EXCEPTION FOR SQLSTATE '99002';
         SIGNAL @exception;
      END;
   END IF;

END;

Here's what happens in dbisql:

CALL p();

Could not execute statement.
User-defined exception signaled
SQLCODE=-297, ODBC 3 State="99002"
Line 1, column 1
CALL p()

The number you pick doesn't have to be unique, that's up to you... you can use 99002 all over the place if you don't mind the aggro of not knowing where it came from.

Here's a sample that includes code for capturing and recording exceptions; in this case, the 99xxx numbers are only unique within the stored procedure because other logic takes care of identifying the procedure:

DECLARE @diagnostic_location                            VARCHAR ( 20 );
DECLARE @sqlcode                                        INTEGER;
DECLARE @sqlstate                                       VARCHAR ( 5 );
DECLARE @errormsg                                       VARCHAR ( 32767 );

...

   SELECT ...

   IF SQLCODE <> 0 THEN
      BEGIN
         DECLARE @exception EXCEPTION FOR SQLSTATE '99034';
         SIGNAL @exception;
      END;
   END IF;

   ...

   UPDATE ...

   IF @@ROWCOUNT <> 1 THEN
      BEGIN
         DECLARE @exception EXCEPTION FOR SQLSTATE '99035';
         SIGNAL @exception;
      END;
   END IF;

   ...

   INSERT ...

   IF @@ROWCOUNT <> 7 THEN
      BEGIN
         DECLARE @exception EXCEPTION FOR SQLSTATE '99036';
         SIGNAL @exception;
      END;
   END IF;

...

EXCEPTION 

   WHEN OTHERS THEN
   
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

      IF @sqlstate LIKE '99___' THEN
         SET @errormsg = STRING ( 'Internal error ', @sqlstate );
      END IF;

      CALL rroad_exception ( STRING ( 
         @diagnostic_location, '(210eh1)', 
         ' SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) );

      RESIGNAL;

For more information about handling exceptions, see these earlier posts:

Tip: The All-Encompassing Try-Catch

Refactoring Foxhound: Logging Exceptions

Dump and Continue

---