Advertisement
AmourSpirit

Handle Mysql error in Stored Procedure

Jul 10th, 2015
289
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.64 KB | None | 0 0
  1. -- http://stackoverflow.com/questions/26760414/mysql-stored-procedure-return-value
  2. -- handle errors in MySql Stored Proceeduer
  3.  
  4. CREATE DEFINER=`root`@`localhost` PROCEDURE `validar_egreso`(
  5. IN codigo_producto VARCHAR(100),
  6. IN cantidad INT,
  7. OUT valido INT(11)
  8. )
  9. BEGIN
  10. DECLARE EXIT HANDLER FOR SQLEXCEPTION
  11. BEGIN
  12.     GET DIAGNOSTICS CONDITION 1
  13.     @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
  14.     SELECT @p1, @p2;
  15. END
  16. DECLARE resta INT(11);
  17. SET resta = 0;
  18.  
  19. SELECT (s.stock - cantidad) INTO resta
  20. FROM stock AS s
  21. WHERE codigo_producto = s.codigo;
  22.  
  23. IF (resta > s.stock_minimo) THEN
  24.     SET valido = 1;
  25. ELSE
  26.     SET valido = -1;
  27. END IF;
  28. SELECT valido;
  29. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement