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.
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:
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.
Results:
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.
Sign up for our newsletter to get access to our upcoming SQL tutorials.
See you soon!!!
If you enjoy our content, please consider buying us a coffee to support our work:
