11

I need to conditionally raise an error, but I can only use a simple statement and no stored procedure.

I'd like to do something like this:

select case when foo = "bar" then 1 else SIGNAL SQLSTATE 'ERROR' end;

Unfortunately SIGNAL is only usable in triggers and procedures and I have to use this within an existing application that only allows me to enter statements, but not procedures. (I only have one long line and no way to set a DELIMITER etc.)

Is there any other way to conditionally cause a runtime error ?

1 Answer 1

14

Retrieve column from a table with multiple rows in the else part

select case when foo = "bar" then 1 else (select table_name from information_schema.tables) end;

As an example, let's use @foo instead of foo

mysql> select case when @foo = "bar" then 1 else (select table_name from information_schema.tables) end;
ERROR 1242 (21000): Subquery returns more than 1 row

Give it a Try !!

1
  • In MySQL 8.0 and MariaDB this is no longer an error - dbfiddle.uk/Ya1_mMWP - empty value is returned instead.
    – mvorisek
    Feb 4 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.