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.
