Mysql stored procedure dynamic select with out variable

Question

When I didn't use out variable, the stored procedure worked correctly, but when I execute the stored procedure with out variable, this error shows up:

MySQL said: #1327 - Undeclared variable: sp_result

Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(OUT `sp_result` INT(11), IN `sp_where_param` VARCHAR(100), IN `sp_where_value` VARCHAR(100), IN `sp_table_name` VARCHAR(100))
NO SQL
BEGIN
DECLARE tbl_id VARCHAR(100);

SET tbl_id = CONCAT(sp_table_name,'_id');

SET @temp1=CONCAT('SELECT count(',tbl_id,') INTO sp_result FROM ',sp_table_name,' WHERE ',sp_where_param,' = \'',sp_where_value,'\'');
PREPARE stmt1 FROM @temp1;
EXECUTE stmt1;

END

Maybe without out variable also doesn't work :(

Devart · Accepted Answer · 2012-12-27 07:43:37Z

1

Try to use user variable -

CREATE DEFINER = 'root'@'localhost'
PROCEDURE test3 (OUT `sp_result` int(11), IN `sp_where_param` varchar(100), IN `sp_where_value` varchar(100), IN `sp_table_name` varchar(100))
NO SQL
BEGIN
  DECLARE tbl_id varchar(100);

  SET tbl_id = CONCAT(sp_table_name, '_id');

  SET @temp1 = CONCAT('SELECT COUNT(', tbl_id, ') INTO @sp_result FROM ', sp_table_name, ' WHERE ', sp_where_param, ' = \'', sp_where_value, '\'');

  PREPARE stmt1 FROM @temp1;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;

  set sp_result = @sp_result;

END

...and add DEALLOCATE PREPARE statement.

answered Dec 27, 2012 at 7:43

Devart

120k23 gold badges172 silver badges189 bronze badges

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

3 Comments

navid Over a year ago

you're right :) .can you tell me what's user variable ? what's different when we use @ ?

Devart Over a year ago

There are: user variables - which are accessible in a session, declared variables - which are declared with a DECLARE command and accessible in BEGIN...END clause, and procedure/function parameters - which can be used in procedure/function only. It seems that parameters cannot be used in prepared statements; so, I suggested user variable.

geoidesic Over a year ago

Great but how to set the type of a user variable? In my tests floats are being rounded to int values using this code.

Suresh Kamrushi · Accepted Answer · 2012-12-27 06:33:44Z

0

it should be-

SET tbl_id = CONCAT(sp_table_name,'_id');
SET @temp1=CONCAT('SELECT count(',tbl_id,') INTO

like this

SET @tbl_id:= CONCAT(sp_table_name,'_id');
SET @temp1:=CONCAT('SELECT count(',tbl_id,') INTO

answered Dec 27, 2012 at 6:33

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

1 Comment

navid Over a year ago

i do everything you said but still doesn't work.ERROR : MySQL said: #1327 - Undeclared variable: sp_result

Collectives™ on Stack Overflow

Mysql stored procedure dynamic select with out variable

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related