CODEWITHSUNDEEP
exceptionstored-proceduressnowflake-cloud-data-platformsql-scripts
Rahul Bhosale
Rahul Bhosale

Reputation: 3

How to handle the exception in Snowflake SQL scripting stored procedure with return type as TABLE

I have created a user-defined table function (UDTF) GET_MEDICATION which accepts an input parameter language_key and returns the data as per the language_key passed in. That is working perfectly fine.

My main problem is with the EXCEPTION section in this stored procedure:

CREATE OR REPLACE PROCEDURE MEDICATION_MODEL_NLS_Test(Language_key VARCHAR DEFAULT 'en')
RETURNS TABLE ()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    res RESULTSET;
    ct_exp VARCHAR;
BEGIN
    res:= (select *
    from TABLE(GET_MEDICATION(:Language_key)));
    RETURN TABLE(res);
EXCEPTION
        WHEN STATEMENT_ERROR OR EXPRESSION_ERROR THEN
            ct_exp := ('SQLCODE ='  || SQLCODE ||', SQLERRM = ' || SQLERRM || ', SQLSTATE = ' || SQLSTATE);
            EXECUTE IMMEDIATE :ct_exp INTO :res;
        RETURN TABLE(res);
END
$$;


CALL MEDICATION_MODEL_NLS_Test('de');

Without the exception part, the stored procedure is working perfectly fine, there is some syntactical issue with the exception handling part. If somebody can help me out here, that would be appreciated.

I'm looking forward to a solution that will help me handle the exception gracefully.

When trying to compile the above stored procedure, it is throwing an error

Syntax error: unexpected 'IMMEDIATE'

enter image description here

Upvotes: 0

Views: 82

Answers (1)

samhita
samhita

Reputation: 3505

You can form your EXCEPTION block like below

EXCEPTION
    WHEN STATEMENT_ERROR OR EXPRESSION_ERROR THEN
        res := (SELECT  :sqlcode as sqlcode,
        :sqlerrm as sqlerrm,
        :sqlstate  as sqlstate
                 ) ;
           return table(res)   ;

I have tested below by raising an exception by using an invalid EXECUTE IMMEDIATE statement

execute immediate 'select' ;

Test Procedure:

CREATE OR REPLACE PROCEDURE MEDICATION_MODEL_NLS_Test(Language_key INT DEFAULT 1000)
RETURNS TABLE ()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    res RESULTSET;
BEGIN
    res := (SELECT 1);
    execute immediate 'select' ;
    RETURN TABLE(res);
EXCEPTION
    WHEN STATEMENT_ERROR OR EXPRESSION_ERROR THEN
        res := (SELECT  :sqlcode as sqlcode,
        :sqlerrm as sqlerrm,
        :sqlstate  as sqlstate
                 ) ;
           return table(res)   ;    
                 
END;
$$;

Upon calling, it returns the output in desired format

enter image description here

Upvotes: 0

Related Questions