Database Administration
oracle memsql singlestore
Updated Wed, 21 Sep 2022 15:51:18 GMT

SingleStore exception no_data_found


Im having difficulty finding the exception no_data_found in singlestore.

In Oracle, we use no_data_found exception. Is there any alternative solution in singlestore for the no_data_found exception?




Solution

If it is inside a stored procedure, you can use ER_INTO_VARIABLES_NO_ROWS. Example is below:

create table test(id int primary key, descr varchar(10));
insert into test values (1,'one'),(2,'two'),(3,'three');
delimiter $$
create or replace procedure test_sp(inp_id int)
returns void as
declare
      v_descr varchar(10);
begin
       begin
         select descr into v_descr from test where id = inp_id;      
         echo select v_descr;
         exception
           when ER_INTO_VARIABLES_NO_ROWS then 
                echo select 'No rows found';
        end;
end $$
delimiter ;
call test_sp(1); -- returns 'one'
call test_sp(100); -- returns 'No rows found'