calling stored procedure with output parameter

Question

Herewith I have given the stored procedure "GetAvgOut":

delimiter //
DROP PROCEDURE IF EXISTS GetAvgOut//
CREATE DEFINER = 'MailIntimator'@'127.0.0.1' PROCEDURE GetAvgOut(OUT average INT,IN col VARCHAR(30),IN tbl VARCHAR(30))
READS SQL DATA
COMMENT 'returns average'
BEGIN
SET @userVar = CONCAT(' SELECT AVG( ' , col , ' ) FROM ' , tbl );
PREPARE stmt FROM @userVar;
EXECUTE stmt;
END;
//
delimiter ;

I tried calling the aforeseen procedure using,

CALL GetAvgOut(@a,'Population','city');
SELECT @a;

"select @a" returns null. How can I get the average which is assigned to out parameter "@a"?

why average is declare as int , and not a float field type ? — Haim Evgi
– Haim Evgi, Commented Jul 15, 2009 at 8:18
try : CALL GetAvgOut(@average,'Population','city'); SELECT @average; — Haim Evgi
– Haim Evgi, Commented Jul 15, 2009 at 8:20

Philip · Accepted Answer · 2009-07-15 08:18:47Z

1

I'm no mysql expert, but do you not need to refer to the OUT variable at any point and assign it a value?

For example as seen on http://dev.mysql.com/doc/refman/5.0/en/call.html:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

HTH

Phil'

answered Jul 15, 2009 at 8:18

Philip

9338 silver badges19 bronze badges

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

Comments

Josh · Accepted Answer · 2009-07-15 08:21:54Z

1

you are not setting the OUT parameter in your select.

try updating your statement to:

SET @userVar = CONCAT('SELECT AVG( ' , col , ' ) INTO average FROM ' , tbl );

answered Jul 15, 2009 at 8:21

Josh

6,2872 gold badges39 silver badges56 bronze badges

1 Comment

jezhilvalan Over a year ago

Thanks for answering. When I did it, "Undeclared variable: average" error is thrown by MySQL. But I have alread declared average while creating procedure CREATE DEFINER = 'MailIntimator'@'127.0.0.1' PROCEDURE GetAvgOut(OUT average INT,IN col VARCHAR(30),IN tbl VARCHAR(30))

thewheat · Accepted Answer · 2014-05-19 09:52:09Z

0

As @Philip mentioned, you need to set the value of the OUT variable. Combining @Josh's and @Philip's answer gives something like this

BEGIN
    SET @userVar = CONCAT(' SELECT AVG( ' , col , ' ) into @average FROM ' , tbl , ' ');
    PREPARE stmt FROM @userVar;
    EXECUTE stmt;
    set average = @average;
END

answered May 19, 2014 at 9:52

thewheat

9887 silver badges13 bronze badges

Collectives™ on Stack Overflow

calling stored procedure with output parameter

3 Answers 3

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Linked

Related