Raise custom error

from the Artful MySQL Tips List


Since MySQL 5.5, MySQL stored routines can raise custom errors using the ISO SQL standard error signalling command SIGNAL.

Suppose you have a tree-read sproc that uses recursive common table expression syntax, available only since MySQL 8.0 and MariaDB 10.2. Before those versions, the sproc can't be created. Your database runs on earlier and more recent MySQL releases.

Write a caller sproc: it calls the tree reader if the server version is adequate, otherwise gracefully reports an error and exits ...

drop procedure if exists treewalk;
delimiter go
create procedure treewalk( root int unsigned )
begin
  declare custom_exception condition for sqlstate '45000';
  if floor( version() ) < 8 then      -- can't run before these versions
    signal custom_exception
    set message_text = 'This procedure requires MySQL 8 or later, or MariaDB 10.2 or later.';
  end if;     
  call treereads( root );
end;
go
delimiter ;

Last updated 16 Aug 2019


Return to the Artful MySQL Tips page