SQL Reference - SIGNAL

SIGNAL

SIGNAL is an SQL statement that is used to raise error conditions programmatically or return a specific condition or error to a calling application. It helps in implementing error control in a stored procedure, functioning as an exception mechanism. After the SIGNAL statement is executed, any further execution of the procedure ceases.

Example

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  -- signal an error
  SIGNAL SQLSTATE '22012' SET MESSAGE_TEXT = 'An error has occurred!';
END;

Output

ERROR 1644 (22012): An error has occurred!

Explanation

In the given example, SIGNAL keyword is used to generate an error. The specific SQLSTATE value ‘22012’ is associated with division by zero. However, here it is used only for example purposes and doesn’t relate to any actual error scenario. If an exception occurs, the error defined in the SIGNAL statement gets triggered and the respective error message is displayed.

Since SQL Server doesn’t support the SIGNAL statement directly, we can use THROW statement to generate an error. This statement is used to throw an exception and transfer execution to a CATCH block of a TRY…CATCH construct in SQL Server.

Example

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Throw an error with error number 50001 and message.
    THROW 50001, 'A divide by zero error occurred.', 1;
END CATCH;

Output

Msg 50001, Level 16, State 1, Line 7
A divide by zero error occurred.

Explanation

The BEGIN TRY block contains a command that will generate a divide-by-zero error. When that error occurs, execution is transferred to the BEGIN CATCH block where the THROW statement is used to raise an exception with a specific error number and message.

Oracle PL/SQL does not provide SIGNAL statement directly similar to MySQL. But you may use RAISE_APPLICATION_ERROR procedure to signal an error condition in your PL/SQL block.

Example

DECLARE
    my_error CONDITION := TRUE;
BEGIN
    IF my_error THEN
        RAISE_APPLICATION_ERROR(-20001, 'An error occurred');
    END IF;
END;
/

Output

ERROR at line 3:
ORA-20001: An error occurred

Explanation

In this example, an error is manually raised using RAISE_APPLICATION_ERROR within a PL/SQL block. The -20001 is a user-defined error number and must be between -20000 and -20999. Following this is the message to accompany the error, in this case ‘An error occurred’. This operation will immediately halt execution of the program when encountered.