Stored Procedure not working in MySQL

Question

DELIMITER $$

DROP PROCEDURE IF EXISTS `pawn`.`simpleproc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `pawn`.`simpleproc`(OUT param1 int, inout incr int)
BEGIN
declare incr Integer;
    set incr= incr+1;
    SELECT count(*) into param1 FROM pawnamount;
 END $$

This is my code to create a stored procedure....It's created.. For execute..

call simpleproc(@param1,@incr);
select @param1,@incr

The Result will be null values.. It is the simple one.. I've tried many times.But,I get null values only..

flupps · Accepted Answer · 2011-05-11 10:55:12Z

1

DECLARE incr INT;                            -- incr is NULL here, add DEFAULT 0  if you want it to have a value
SET incr = incr + 1                          -- NULL + 1 is still NULL
SELECT COUNT(*) INTO param1 FROM pawnamount; -- If the table pawnamount is empty, it generates an empty set, which in a parameter assignment becomes NULL.

answered May 11, 2011 at 10:55

flupps

211 bronze badge

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

1 Comment

Ike Walker Over a year ago

count(*) will never return NULL. It will return 0 if the table is empty.

Ike Walker · Accepted Answer · 2011-05-11 15:01:09Z

1

Since you define incr as an INOUT paramater, you should not declare it again in the body of your procedure. This way you can increment it properly as long as it is initialized before being passed to your procedure.

Here's the code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `pawn`.`simpleproc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `pawn`.`simpleproc`(OUT param1 int, inout incr int)
BEGIN
    set incr= incr+1;
    SELECT count(*) into param1 FROM pawnamount;
 END $$

DELIMITER ;

set @incr = 0;
call simpleproc(@param1,@incr);
select @param1,@incr;

answered May 11, 2011 at 15:01

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Collectives™ on Stack Overflow

Stored Procedure not working in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related