CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Philipp_Kats
Philipp_Kats

Reputation: 4214

make SQL query fail on condition

For XYZ reason I need a query to explicitly fail (return error code to connection) if some condition is met (on Snowflake). Can someone recommend an approach?

Some illustration in pseudo-code:

IF 0= ( SELECT COUNT(*) FROM XYZ) THEN FAIL

Upvotes: 1

Views: 702

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11086

I like Simeon's approach, but you may want a custom error message if this is running in a long script. Throwing an error in a JavaScript UDF will allow custom (if untidy) error messages:

create or replace function RAISE_ERROR(MESSAGE string)
returns string
language javascript
as
$$
    throw "-->" + MESSAGE + "<--";
$$;

select 
    case (select count(*) from XYZ)
        when 0 then raise_error('My custom error.')
        else 'There are rows in the table'
    end
;

If there are no rows in XYZ, it will generate an error message that reads:

JavaScript execution error: Uncaught --> My custom error <--. in RAISE_ERROR at ' throw MESSAGE;' position 4 stackstrace: RAISE_ERROR line: 2

It's not the tidiest of error messages, but it will allow you to embed a custom error message if you need help identifying the error. The arrows should help direct people to the real error message thrown in the stack.

Upvotes: 3

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

SELECT IFF(true, 1::number, (1/0)::number);

then:

IFF(TRUE, 1::NUMBER, (1/0)::NUMBER)
1

where-as

SELECT IFF(false, 1::number, (1/0)::number);

gives:

Division by zero

Upvotes: 2

Related Questions