There are two stored procedures.

1 procedure

CREATE DEFINER=`******`@`******` PROCEDURE `add_row_into_number_docs`(IN `dealer` INT, IN `docTypeId` INT) LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT '' num_docs:BEGIN

I will not give all the code, since it is big. I note only that it should result in the result variable, which takes the value 0 or 1

 select result; END

2 procedure

 CREATE DEFINER=`******`@`******` PROCEDURE `oparation`(IN `dealer` INT, IN `docTypeId` INT) LANGUAGE SQL DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT '' num_docs:BEGIN

Also, I will not give all the code. This procedure calls 1 procedure.

 call add_row_into_number_docs(userDealerId,docTypeId);

And then there are again some manipulations. This procedure should return the result:

 select resultCode, resultComment, idKOOperation; END

But for some reason, when calling the second procedure, I get a result that returns 1 procedure

 result

Instead of the expected

 resultCode, resultComment, idKOOperation

With what it can be connected?

  • If you simply wrote select inside a stored procedure, this has nothing to do with returning values ​​from the stored procedure. To return the result, OUT parameters are used, which you do not have. - Aries
  • OK, I agree. but until I used the first procedure, everything worked fine and I also tried to use OUT parameters, only after introducing them I get the error 'SQL error (1318): Incorrect number of arguments for PROCEDURE oparation; expected 9, got 6 ' - Ibragimhalil Nasrutdinov
  • Thanks, it turns out there are certain nuances of working with output parameters. Understood. - Ibragimhalil Nasrutdinov
  • If possible, publish the solution found in response to your question . I am sure it will help many of your colleagues in the future. - Nicolas Chabanovsky ♦
  • When I called the stored procedure, I specified only IN parameters, It turns out that it was necessary to specify variables in which the output parameters would be saved. In my case, the oparation procedure (IN dealer INT, IN docTypeId OUT resultCode INT) needed to be called a call operation (1.1, @ a) and I initially called by specifying just the input parameters of the call operation (1.1,) - Ibragimhalil Nasrutdinov

1 answer 1

If you want to transfer a value from one procedure to another, it is better to use stored functions instead of stored procedures. In this case, you can transmit

 CREATE DEFINER=`******`@`******` FUNCTION `add_row_into_number_docs`(IN `dealer` INT, IN `docTypeId` INT) RETURNS INT READS SQL DATA COMMENT '' num_docs:BEGIN ... RETURN result; END

Then, inside the oparation stored procedure oparation you can get the value from the add_row_into_number_docs function, assigning it to some variable.

 SET resultNumberCode := add_row_into_number_docs(userDealerId, docTypeId);

If you want to save oparation as a stored procedure, then the result can be included in the resulting SELECT query

 SELECT resultCode, resultComment, idKOOperation, resultNumberCode;