Development

Standard SQL Error Functions

Captain Salem 2 min read

Standard SQL Error Function

Errors are part of every developers life. One way or another, you will come across an instance where you need to debug your application or implement error handling operations in your applications.

It is therefore a good technique to ensure you have a few tools under your belt to aid when debugging your apps. For this tutorial, we will focus on teaching you how to use the error() function in Standard SQL.

What is Standard SQL?

If you have never heard of Standard SQL, it’s basically a native version of the Structured Query Language, also known as ANSI SQL. Database engines such as MySQL, PostgreSQL, Oracle, MariaDB, etc borrow from ANSI SQL with each engine adding, removing or changing various features of the language.

Therefore, if you wish to learn SQL commands and statements that can be easily ported into almost every version of SQL database, Standard SQL is a very good choice.

We will be putting out a series of Standard SQL tutorials, sign up to receive those when they arrive.

Standard SQL Error Function.

The error() function in Standard SQL allows you to return an error. The following code example shows the syntax of the error() function.

ERROR(error_message)

The function takes the error_message as an argument of type STRING.

The function will then return the provided string as an error similar to an error produced by the internal engine. The return type of the function is inferred from context.

Example Function Usage

The following code shows the example of how we can use the error() function.

SELECT
  ERROR("this is an error message");

The statement above should print the provide message as an error as shown in the output below:

image-20220929232201389
image-20220929232201389

Example 2 - Using error function with the WHERE Clause

In the example below, we use the error function in conjuction with the where clause.

SELECT *
FROM (SELECT 0 AS val)
WHERE val > 0 AND ERROR('also an error');

In this case, the statement above can run the error() function before or after the where clause. This stems from the functionality that SQL does not provide order guarantee for the error() function or the where clause.

Example 3 - Using error() function with an IF statement.

If you wish to ensure that the error function only runs when a given condition fails, you can use the if statement as shown in the example below:

SELECT
  *
FROM (
  SELECT
    -1 AS val)
WHERE
IF
  (val > 0, TRUE, ERROR('Fail: Expected Positive Value'));

In the above example, the error() function will only execute if the provided value is less than 0 (negative value). Otherwise, if the value is greater than 1, the if block becomes true and the error() function never executes.

However, in this case, the value is negative, forcing the error() function to run.

And there you have it.

Ending…

Thank you for tuning in to our tutorials. In this post, you discovered how to create error messages using the error() function in Standard SQL.

We hope you enjoyed this article. Leave a comment and share.

Share
Comments
More from Cloudenv

Cloudenv

Developer Tips, Tricks and Tutorials.

Great! You’ve successfully signed up.

Welcome back! You've successfully signed in.

You've successfully subscribed to Cloudenv.

Success! Check your email for magic link to sign-in.

Success! Your billing info has been updated.

Your billing was not updated.