SQLCODE returns 0 in the catch block even the query failed due to invalid identifier

SQLCODE is used in Snowflake Scripting to capture the error code returned from running a certain query so the script can decide how to handle the error. Recently I have seen some users reported that the SQLCODE returned 0 even though the query failed.

I was able to demonstrate the issue using the below script:

create or replace table test (a int);

BEGIN
  -- this should fail because the table has been created before
  CREATE TABLE test (a INT);

  EXCEPTION 

  WHEN OTHER THEN
    ROLLBACK;
    RETURN SQLCODE;
END;

The above script will return the value of “0” after execution, even though CREATE TABLE failed due to table already exists.

The issue here is that the SQLCODE was used after calling the ROLLBACK, so the value of SQLCODE actually refers to the result of the ROLLBACK, not the CREATE TABLE statement.

To fix the issue, we need to store the SQLCODE before calling the ROLLBACK. The solution as below:

DECLARE
  sql_code VARCHAR;
BEGIN
  -- this should fail because the table has been created before
  CREATE TABLE test (a INT);

  EXCEPTION 
  WHEN OTHER THEN
    sql_code := SQLCODE;
    ROLLBACK;

    RETURN sql_code;
END;

It will now return the correct error code of 2002.

Leave a Reply

Your email address will not be published.