0

I hope if someone can help me.

I have this problem when trying to do a proc in MySQL is this way:

DROP PROCEDURE IF EXISTS `insertprivilegio`;
CREATE PROCEDURE `insertprivilegio`(
      IN sprol varchar(50)
) BEGIN
    set @verify = (select rol from privilegios where rol = sprol );
    IF @verify > 0 THEN
        select 'false';
    END IF;
END;

I am trying to verify if there is a role in the privileges table at the time of executing it. It allows me to enter the first data, but if I try to register a new record to know if the verification is valid I get this error:

ER_SUBQUERY_NO_1_ROW: Subquery returns more than 1 row

I would appreciate your opinions to know what I'm doing wrong, thanks.

  • Since this is an English site you are more likely to get an answer if you formulate your question in English. – Lennart Feb 9 at 17:15
  • My guess, rol is not unique in privilegios, so you get a set of rows instead of a single row in your sub-select. – Lennart Feb 9 at 17:23
  • 1
    And why are you comparing rol, which is presumably a varchar(50) value, with an integer literal? – mustaccio Feb 9 at 18:52
  • Try replacing the entire procedure body with RETURN NOT EXISTS( SELECT 1 from privilegios where rol = sprol ); Also, perhaps you want a FUNCTION, not a PROCEDURE? – Rick James Feb 15 at 0:11
-1

thanks friend I have already solved it since I just needed to know if that role existed I did it that way

DROP PROCEDURE IF EXISTS `insertprivilegio`;
CREATE PROCEDURE `insertprivilegio`(
      IN sprol varchar(50)
) BEGIN
    set @verify = (select count(rol) from privilegios where rol = sprol );
    IF @verify > 0 THEN
        select 'false';
    END IF;
END;

and ready

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