how to set a dynamic error message in MySQL stored procedure

Question

Hello I'm trying to create a stored procedure to insert data int to the DB, but first I validate some of the parameters

if one or more of the validation is not met I want to raise an Exception and stop the stored procedure.

So far I can do this by exiting the execution as soon as the firs error is detected doing this:

    -- si existe el correo
            if exists(select 1 from tbl_users u where u.`e-mail` = p_email limit 1) then
               SIGNAL SQLSTATE '45000'
               SET MESSAGE_TEXT ='Error: el correo ingreasdo ya existe';
            end if;
            -- convinacion de nombre y apellido ya existen
            IF EXISTS(SELECT 1 FROM tbl_users u WHERE u.`Nombre` = p_Nombre and u.`Apellido`=p_Apellido limit 1  ) THEN
               SIGNAL SQLSTATE '45000'
               SET MESSAGE_TEXT ='El nombre y apellido ya fueron ingreasados anteriormente';
            END IF;
            -- si el pasword es valido y el tipo (p_fk_user_type_id) es:1 (supervisor)
            IF (SELECT CHAR_LENGTH(p_Password)<4) and (p_fk_user_type_id <>1 ) THEN
               SIGNAL SQLSTATE '45000'
               SET MESSAGE_TEXT ='El pasword es muy corto, debe tener al menos 4 caracteres';
            END IF;

What I need to do is to get all the errors and return them in a single error message something like this:

 DECLARE mensaje VARCHAR(100);
        SET mensaje = '';
        IF EXISTS(SELECT 1 FROM tbl_users u WHERE u.`e-mail` = p_email LIMIT 1) THEN
           SET mensaje = 'el correo ingreasdo ya existe');
        END IF;
        -- convinacion de nombre y apellido ya existen
        IF EXISTS(SELECT 1 FROM tbl_users u WHERE u.`Nombre` = p_Nombre AND u.`Apellido`=p_Apellido LIMIT 1  ) THEN
           SET mensaje = CONCAT(mensaje,' ','El nombre y apellido ya fueron ingreasados anteriormente');
        END IF;
        -- si el pasword es valido y el tipo (p_fk_user_type_id) es:1 (supervisor)
        IF (SELECT CHAR_LENGTH(p_Password)<4) AND (p_fk_user_type_id <>1 ) THEN
           SET mensaje = CONCAT(mensaje, ' ' ,'El pasword es muy corto, debe tener al menos 4 caracteres');
        END IF;

        IF (mensaje <>"")THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = mensaje;
         ELSEIF 
           -- insert to database
        END IF;

What is the issue? What is the error message?

Ivan Cachicatari
– Ivan Cachicatari

2014-12-12 03:25:50 +00:00
Commented Dec 12, 2014 at 3:25 — Ivan Cachicatari
– Ivan Cachicatari, Commented Dec 12, 2014 at 3:25

lrkwz · Accepted Answer · 2021-11-19 11:18:57Z

0

You can try SET @mensaje = CONCAT(....) as suggested here.

The docs (and here) suggest that mensaje will be treated as a parameter passed to your procedure and @mensaje is the proper way to DECLARE a user variable.

answered Nov 19, 2021 at 11:18

lrkwz

6,6124 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to set a dynamic error message in MySQL stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related