CODEWITHSUNDEEP
mysqlstored-procedures
Charul
Charul

Reputation: 252

why stored proc always returning 0 in mysql

I'm trying to create a stored proc that will take two input parameters 1.Table name 2. Column name and find the maximum from the column. The stored proc is created successfully in DB.Here is the definition

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GETMAXIMUMID`(IN `tab_name` VARCHAR(40), IN `col_name` INT(10))
BEGIN
 SET @t1 =CONCAT('SELECT max(',col_name,') FROM ',tab_name );
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END$$
DELIMITER ;

Now I am calling it as

CALL GETMAXIMUMID('tbl_segment','id');

but it is always returning 0 as result for every table.

Please let me know where I am wrong.I am trying stored proc for the first time and this is what I have learnt in few days.

Upvotes: 0

Views: 34

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562398

You declared col_name is an integer parameter, but you pass 'id' as the value.

MySQL converts a string to an integer by reading any initial digit characters and ignoring the rest. If there are no digit characters, the value is converted to 0.

So you are preparing and executing this query:

SELECT MAX(0) FROM tbl_segment

Which naturally returns zero.

Upvotes: 3

Akina
Akina

Reputation: 42661

CREATE 
/* DEFINER=`root`@`localhost` */
PROCEDURE `GETMAXIMUMID`(IN `tab_name` VARCHAR(64), IN `col_name` VARCHAR(64))
BEGIN
 SET @t1 =CONCAT('SELECT max(',col_name,') FROM ',tab_name );
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END

fiddle

Upvotes: 2

Related Questions