THROW_UDP

Description

Raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct.

SQLServer syntax

THROW [ { error_number | @local_variable },  
        { message | @local_variable },  
        { state | @local_variable } ]   
[ ; ]  

Custom UDP

Keeps the same parameters as the original procedure

UDP

-- <copyright file="THROW_UDP.sql" company="Snowflake Inc">
--        Copyright (c) 2019-2023 Snowflake Inc. All rights reserved.
-- </copyright>

-- =======================================================================================================
-- Description: THROW statement raises an exception in SQL Server.
-- Parameters:
--   error_number: Is a constant or variable that represents the exception. error_number is int.
--   message:      Is a string or variable that describes the exception.
--   state:        Is a constant or variable between 0 and 255 that indicates the state to associate with the message.

-- Return:         Throws an exception in runtime.
--
-- Note:            In Snowflake a custom exception must be defined with some ID which is an integer between ]-20999, -20000[. -20001 is used by default.
-- =======================================================================================================

CREATE OR REPLACE PROCEDURE THROW_UDP(ERROR_NUMBER INT, MESSAGE string, STATE INT)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
USER_EXCEPTION_SQL VARCHAR;
BEGIN
        USER_EXCEPTION_SQL:='DECLARE USER_EXCEPTION EXCEPTION 
        (-20001, \'\n'
        ||'ERROR NUMBER: ' ||ERROR_NUMBER 
        || '\nMESSAGE: ' || MESSAGE 
        || '\nSTATE: ' || STATE ||'\'); '
        || 'BEGIN ' 
        ||      ' RAISE USER_EXCEPTION; '
        || 'END;' ;
EXECUTE IMMEDIATE :USER_EXCEPTION_SQL;
END;
$$;

SQL Server

Query

THROW  50000, 'DIVISION BY 0', 1;

Output

[S0001][50000] Line 7: DIVISION BY 0

Snowflake

Query

CALL THROW_UDP(50000, 'DIVISION BY 0', 1);

Output

ERROR NUMBER: 50000
MESSAGE: DIVISION BY 0
STATE: 1