CODEWITHSUNDEEP
mysqlstored-procedures
Catalina
Catalina

Reputation: 101

Stored procedure OUT returns null value

My stored procedure always returns a null value when I try to select it. I did some research and I have to specify somewhere OUTPUT. Not sure where or how to do that.

delimiter $$

Create Procedure ClientPurchases (IN idClient INT, outClientAvgPurchases DECIMAL (4,2))
BEGIN 

DECLARE PurchasesAvg DECIMAL(4,2) ;

set PurchasesAvg=
(SELECT AVG(PurchaseAmount) 
FROM Purchase
inner join Client on idClient=Client_idClient);
SET outClientAvgPurchases= PurchasesAvg;

END$$


CALL ClientPurchase(3, @purchase );

SELECT  @purchase as 'Purchase Total';

Upvotes: 1

Views: 190

Answers (2)

shmosel
shmosel

Reputation: 50716

OUT is a keyword that should precede the parameter name:

Create Procedure ClientPurchases (IN idClient INT, OUT ClientAvgPurchases DECIMAL (4,2))
...
SET ClientAvgPurchases= PurchasesAvg;

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562250

You can declare a procedure argument as IN, OUT, or INOUT.

Create Procedure ClientPurchases (
    IN idClient INT, 
    OUT outClientAvgPurchases DECIMAL (4,2))
  . . .

Read more about this at http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

Upvotes: 2

Related Questions