How to raise user defined error in snowflakes

Ponmathi Radhakrishnan 0
Viewed 22

I am trying to raise the user defined error in snowflake using LANGUAGE JAVASCRIPT, how to raise the error, can you please help me with that

try
{
 declare
        exception_1 exception (-20001, 'no record in table');
begin
var output_trunc_query = `select count(*) from xyz`;
var output_trunc_stmt = snowflake.createStatement({ sqlText: output_trunc_query});
var output_trunc = output_trunc_stmt.execute();
var return_count = ""
while (output_trunc.next())  {
    return_count += output_trunc.getColumnValue(1);
}
if(return_count>0)
{
var output_trunc_query = `delete from xyz`;
var output_trunc_stmt = snowflake.createStatement({ sqlText: output_trunc_query});
var output_trunc = output_trunc_stmt.execute();
 return "Succeeded";
 }
 else
 {
 raise exception_1;
 }
 end
 }
 catch (err)  
{
return "Fail";
}
java snowflake-cloud-data-platform
1 Answers

You can't mix Snowflake Scripting (SQL) code and JavaScript. I don't understand why you need to raise a user defined exception if you will catch it in the same SP. Anyway, I removed try/catch to see the exception I raised, and here is the code:

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    var output_trunc_query = `select count(*) from xyz`;
    var output_trunc_stmt = snowflake.createStatement({ sqlText: output_trunc_query});
    var output_trunc = output_trunc_stmt.execute();
    var return_count = ""
    while (output_trunc.next())  {
        return_count += output_trunc.getColumnValue(1);
    }
    if(return_count>0)
    {
        var output_trunc_query = `delete from xyz`;
        var output_trunc_stmt = snowflake.createStatement({ sqlText: output_trunc_query});
        var output_trunc = output_trunc_stmt.execute();
        return "Succeeded";
     }
     else
     {
         var exception_stmt = `declare
               exception_1 exception (-20001, 'no record in table');
         begin   
               raise exception_1;
         end;`;
         snowflake.createStatement({ sqlText: exception_stmt}).execute();
         
     }

$$
;


call test_sp();

Fails with:

Uncaught exception of type 'EXCEPTION_1' on line 4 at position 15 : no record in table
At Statement.execute, line 23 position 63
Gokhan Atil0
Related