CODEWITHSUNDEEP
mysql
Regina
Regina

Reputation: 11

Stored Procedure not working in MySQL

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..

Upvotes: 1

Views: 1237

Answers (2)

Ike Walker
Ike Walker

Reputation: 65527

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;

Upvotes: 1

flupps
flupps

Reputation: 21

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.

Upvotes: 1

Related Questions