Providing Return Values

In DB2 and SQL Server, you can provide a return value for a stored procedure using the RETURN keyword:

RETURN value;

However, you can only return scalar values in this way, and you can only return integers.

When a RETURN statement is encountered in a stored procedure, the procedure terminates immediately, and no further lines of code execute. Bear in mind that this may mean that some output parameters aren't assigned values.

Return values are used to indicate whether the stored procedure executed successfully. Normally, you'd return zero for successful execution or an error code if something went wrong.

SQL Server Error Handling

In SQL Server, you can use the @@ERROR global to check if an error occurred (globals are special variables that are accessible to all code accessing a database, and in general they start with @@). If no error has occurred, then @@ERROR will have a value of 0; otherwise, it'll have an integer value reflecting the error that has occurred. You can check for this as follows:

IF (@@ERROR <> 0)
   ...Do something, because an error has occurred...

You might choose to exit the procedure, for example, or save the error value to an output parameter for later inspection.

In addition, you can raise your own errors if, for example, you only allow certain values for a parameter and the procedure call uses a value that isn't allowed. You can do this using the RAISERROR statement:

RAISERROR { msg_id | msg_str }, severity, state [, argument]]

If a msg_id is specified, rather than a string error message, it must be between 13,000 and 2,147,483,647, and the message must be defined in the sysmessages system table. The severity is a value from 0 to 25 that indicates how serious the error is—values from 20 onward are fatal errors and will cause the connection to be lost. Values from 19 onward are reserved for users in the sys-admin role. The next parameter, state, is an arbitrary integer value between 1 and 127 that allows you to return information about the state of the error. Finally, you can also specify arguments that are passed into the message string. This can be used to return information about the specific values that caused the error. If any arguments are added, the message string needs to contain markers to indicate where the arguments are to be placed. These markers consist of a percent sign and a character indicating the type of the argument; for example, %d represents a signed integer, %u an unsigned integer, and %s a string.

Let's look at a quick example. You'll create a simple stored procedure that inserts a row into the Students table and call this twice, passing in the same details.

ERROR HANDLING (SQL SERVER)

Create the following stored procedure:

CREATE PROCEDURE ErrorTest(@i_StudID INT,
                             @i_StudName VARCHAR(10))
AS
BEGIN
   DECLARE @errno INT;
   INSERT INTO Student VALUES (@i_StudID, @i_StudName);
   SET @errno = @@ERROR;
   IF @errno <> 0
   BEGIN
      RAISERROR (
         'Can''t insert row with ID %d into database', 10,
         1, @i_StudID);
      RETURN @errno;
   END;
   ELSE
      RETURN 0;
END;

This procedure simply inserts a new row into the Student table, based on the parameters passed in. Once you've executed the INSERT statement, you store the @@ERROR value because this will change if further statements are executed. You then check the value of this variable; if it's not zero, you raise an error, giving the ID of the row that caused the problem. You then return the error number as the return value of the procedure. If everything went well, you just return zero.

Test this procedure by executing these two lines in Query Analyzer:

EXEC ErrorTest 99, 'John Fields';
EXEC ErrorTest 99, 'Charles Ives';

The first statement will execute without problems, but the second will generate an error because there's already a row with the ID 99:

   (1 row(s) affected)

   Server: Msg 2627, Level 14, State 1, Procedure ErrorTest, Line 7
   Violation of PRIMARY KEY constraint 'PK__Student__1920BF5C'.
   Cannot insert duplicate key in object 'Student'.
   The statement has been terminated.
   Can't insert row with ID 99 into database

An error is raised before you check the error number, but execution continues (this won't always happen but depends on the severity of the original error). The custom error is then raised, so your own error message appears as the last line of the output.

Oracle Error Handling

In Oracle, you handle errors by placing an EXCEPTION block at the end of a BEGIN block (immediately before the END keyword). Within this EXCEPTION block, you can write code that will execute whenever a certain error occurs.

BEGIN
    -- SQL code here
EXCEPTION
   WHEN Exception1 THEN
      -- Handle Exception1
   WHEN Exception2 THEN
      -- Handle Exception2
   -- Handle other exceptions
END;

You can define your own exceptions and handle them here, but there are also a number of predefined exceptions. The most common are the following:

• CURSOR_ALREADY_OPEN: This exception is raised if you try to open a cursor that's already open.

• DUP_VAL_ON_INDEX: This is raised if you try to insert a row with a duplicate value in a primary key column or a column with a unique index.

• INVALID_NUMBER: This is raised when you try to convert a string into a number if the string doesn't contain a valid numerical value.

• NO_DATA_FOUND: This exception is raised if you use a SELECT INTO statement to store a value in a variable, but no row is returned by the SELECT query.

• TOO_MANY_ROWS: If you try to use a SELECT INTO statement to populate a variable but the SELECT statement returns more than one row, a TOO_MANY_ROWS exception will be thrown.

• OTHERS: Handles any exceptions not handled by any of the previous exception handlers.

To define your own exception, you simply declare a variable of type EXCEPTION and then RAISE that exception when a particular condition is met. You handle custom exceptions in exactly the same way as predefined exceptions.

Let's look at an example of this in action.

ERROR HANDLING (ORACLE)

Create the following stored procedure:

CREATE OR REPLACE PROCEDURE ErrorTest(
                                       i_StudID   IN INT,
                                       i_StudName IN VARCHAR)
AS
   UnluckyNumber EXCEPTION;
BEGIN
   IF i_StudID = 13 THEN
      RAISE UnluckyNumber;
   END IF;
   INSERT INTO Student VALUES (i_StudID, i_StudName);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      dbms_output.put_line(
         'A student already exists with ID ' || i_StudID);
   WHEN UnluckyNumber THEN
      dbms_output.put_line(
         'Can''t insert a student with an unlucky ID');
END;
/

In this procedure, you insert a new row into the Student table. You check for two exceptions—the predefined DUP_VAL_ON_INDEX exception, which will be thrown if you try to insert a row with a StudentID that already exists in the table, and a custom exception called UnluckyNumber, which is thrown if you try to insert a row with a StudentID of 13 (just to protect the feelings of any superstitious students).

You implement this by declaring UnluckyNumber as an EXCEPTION in your procedure's declarations section and then checking the value of the i_StudID input parameter before you make the INSERT. If this is 13, you raise the following error:

IF i_StudID = 13 THEN
   RAISE UnluckyNumber;
END IF;

Within the exception handlers, you just write an error message to the output.

Test this procedure by executing this line in SQL*Plus:

CALL ErrorTest(10, 'John Fields');

A student with an ID of 10 already exists, so the DUP_VAL_ON_INDEX exception will be raised:

   A student already exists with ID 10

Now enter the following statement:

CALL ErrorTest(13, 'Charles Ives');

This will cause the UnluckyNumber exception to be raised:

   Can't insert a student with an unlucky ID

DB2 Error Handling

DB2 uses two variables to handle errors in stored procedures—SQLSTATE, which contains a five-character string representing the standard SQL state code for the current error status, and SQLCODE, which is an integer. You can only use one of these because accessing either will cause the other to be reset.

See the DB2 Message Reference at http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7m0frm3toc.htm for a complete list of the SQL state codes supported by DB2.

Before you use either of these variables, you need to declare them in the procedure. Usually you would initialize them with default values that indicate all is well:

DECLARE SQLCODE  INT      DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

By default, any exception thrown will cause the stored procedure to stop executing, so you have to handle errors if you want execution to continue. To do this, you need to set up a handler for that exception. You do this using the DECLARE...HANDLER statement:

DECLARE handler_type HANDLER FOR error_type
BEGIN
   -- handler code
END;

There are three types of handler you can use:

• CONTINUE: Continue handlers execute the code in the handler and then continue execution after the line that caused the error. You used a continue handler in the example previously where you looped through a cursor.

• EXIT: If you define an exit handler for an error, then execution will continue after the current BEGIN block once the code in the handler has run. If the current block is nested within another BEGIN block, the stored procedure will continue the execution of the outer block.

• UNDO: This can only be used within a transaction (see the next chapter for a discussion of transactions). If an error handled by an undo handler occurs, any changes made by the current transaction will be rolled back, and execution will continue at the end of the current BEGIN block.

The error_type specifies the type of error associated with this handler. You can handle a specific SQL state, or you can handle one of the following general conditions:

• SQLEXCEPTION: The handler will be invoked whenever a SQL exception is raised.

• SQLWARNING: The handler will be invoked whenever a SQL warning is raised.

• NOT FOUND: This is raised when a WHERE clause matches no rows in the database.

For example, if you want to handle SQL state '23505', which occurs if you try to insert a duplicate value into a primary key or unique column, and you want to continue execution at the next statement, you use the following:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
   -- Do something here to handle the error

You can raise custom errors using the SIGNAL statement:

SIGNAL SQLSTATE SqlStateCode
   SET MESSAGE_TEXT = ErrorDescription;

This raises an error with the specified SQL state and error message, which will be returned to the client application if it isn't handled by an exception handler. Note that you can only use SQL state codes beginning with characters in the range '7' to '9' or 'T' to 'Z' for custom exceptions.

Let's see an example to see how this works in practice.

ERROR HANDLING (DB2)

Build the following stored procedure:

CREATE PROCEDURE DB2ADMIN.ErrorTest (
                                  i_StudID   INT,
                                  i_StudName VARCHAR(50),
                              OUT o_Status   CHAR(5))
P1: BEGIN
   DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
      SET o_Status = SQLSTATE;
   IF i_StudID = 13 THEN
      SIGNAL SQLSTATE '75000'
         SET MESSAGE_TEXT =
             'Can''t insert a student with an unlucky ID';
   END IF;
   INSERT INTO Student VALUES (i_StudID, i_StudName);
   SET o_Status = SQLSTATE;
END P1

Here you define just one error handler—an exit handler for all SQL exceptions. In it, you just set the value of the o_Status output parameter to the SQL state associated with the error:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
   SET o_Status = SQLSTATE;

You then check to make sure that no unfortunate student has been assigned the unlucky ID number 13, and if they have, you raise a custom error:

IF i_StudID = 13 THEN
    SIGNAL SQLSTATE '75000'
       SET MESSAGE_TEXT =
           'Can''t insert a student with an unlucky ID';

Finally, you execute the INSERT statement and return the resulting SQL state as an output parameter.

After building this procedure, test it by entering the following statement into Command Center:

CALL ErrorTest(10, 'Zdenek Fibich', ?)

A SQL exception is raised here because a student with the ID of 10 already exists. However, because it's handled by your exit handler, the error isn't passed directly back to the user. Instead, you see the relevant SQL state in your output parameter:

   Value of output parameters

   --------------------------
   Parameter Name  : O_STATUS
   Parameter Value : 23505

   Return Status = 0

Now try entering a student with an ID of 13:

CALL ErrorTest(13, 'Rasmus Rask', ?)

This will cause your custom error to be raised, but because all SQL exceptions are handled by the exit handler, this error isn't passed on to the client. Instead, you can see the SQL state for your error in the output parameter:

   Value of output parameters

   --------------------------
   Parameter Name  : O_STATUS
   Parameter Value : 75000

   Return Status = 0

If you hadn't handled the error, the exception would have been raised back to the client, as shown in Figure 9-10.

Figure 9-10: Raising the exception back to the client

A Final Note on Stored Procedures

There's a school of thought that says all database access should be carried out through stored procedures. The reason for this is that this enhances security and allows the RDBMS you're using to optimize itself for the use of the stored procedures it contains, without having to interpret new SQL statements. Also, because stored procedures are usually precompiled inside the RDBMS, this is another reason for enhanced performance.

One major advantage is that using stored procedures avoids the risk of socalled SQL insertion attacks. Suppose you have a standard SELECT query where a parameter is taken straight from user input. It's possible for that user input to perform operations you really don't want it to perform. Rather than inserting a literal value, you might find that a malicious user inserts an entire SQL statement, perhaps one that would delete all the tables in your database. As you've seen, multiple statements can be executed in a single batch, so this is possible. If you accidentally assign too many security privileges to users, they may just destroy your data. Using stored procedures, on the other hand, avoids this possibility because parameters won't be interpreted as entire SQL statements.