Exception using TRY CATCH in mysql stored procedure

Question

Following is the procedure in which i am trying to catch the exception, if there is any in stored procedure

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test( IN contextFilter TEXT)
   BEGIN TRY


  set  @sub_query = 'SELECT id from test_table';

   PREPARE stmt_query FROM @sub_query;
      SELECT @sub_query;
                EXECUTE stmt_query;
                DEALLOCATE PREPARE stmt_query;

   END TRY //
   BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
  END CATCH

 DELIMITER ;

When i try to source the stored procedure , i am getting the following exception

source /home/test.sql;
Query OK, 0 rows affected (0.01 sec)

ERROR 1064 (42000) at line 5 in file: '/home/test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'set  @sub_query = 'SELECT id from test_table';

   PREPARE stmt_query FROM @' at line 5
ERROR 1064 (42000) at line 17 in file: '/home/test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
' at line 1

Answer 1

The post above helped me, but I still had to do some research on my own. In order to provide a complete example you can see below. This code will declare a handler for "catching" SQLExceptions and then exit if it occurs.

DROP PROCEDURE IF EXISTS `SP_TEST`;
DELIMITER //
CREATE PROCEDURE `SP_TEST`()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN 
        GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        SELECT CONCAT(@p1, ':', @p2);
    END;

    SELECT FAKE_COLUMN FROM MY_TABLE;
END //

CALL SP_TEST;

This works in mySQL version 5.7 and hope it helps you.