how to store returning values of a stored procedure in mysql

Question

Though there are many questions in Stack Overflow regarding this problem I didn't able to figure it out properly, could anyone can help me to figure this out, let's say that there are two stored procedures as,

CREATE PROCEDURE `procOne`(stName varchar(7),fullItemCode varchar(12), fullLedNo varchar(12))
BEGIN
set @x = concat('
SELECT ID, Uprice FROM ',stName,' 
where Uprice > 0 and FulItmCode="',fullItemCode,'" and FullLedgerNo = "',fullLedNo,'" order by Dat desc limit 1;
');
PREPARE stmt FROM @x;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

and

CREATE PROCEDURE `prcTwo`()
BEGIN

CREATE TEMPORARY TABLE tmpStore (
  ID int(10),
  Uprice decimal(18,2)
)

insert into tmpStore exec procOne(@param1, @param2) 

select * from tempStore; 

DROP TABLE tmpStore;

END

I have tried this, but am getting the following error when attempting to save prcTwo

ERROR 1064: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near
'insert into tmpStore exec procOne 'St_BFG','SCI019', 'BIO-JVS-30' output
sele' at line 9 SQL Statement: CREATE DEFINER=user1@localhost PROCEDURE 
prcTwo() BEGIN CREATE TEMPORARY TABLE tmpStore ( ID int(10), Uprice 
decimal(18,2) ) insert into tmpStore exec procOne 'St_BFG','SCI019', 
'BIO-JVS-30' output  select * from tempStore; DROP TABLE tmpStore; END

I can't see why you need the second procedure as it stands? Why not just invoke `procOne`? Also at present you haven't all the arguments `procOne` expects defined in `prcTwo`. Please detail further what you are trying to do, and what error or what unexpected results you are getting. — Simon at The Access Group, Feb 24 '12 at 11:52
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into tmpStore exec procOne 'St_BFG','SCI019', 'BIO-JVS-30' output sele' at line 9 SQL Statement: CREATE DEFINER=`user1`@`localhost` PROCEDURE `prcTwo`() BEGIN CREATE TEMPORARY TABLE tmpStore ( ID int(10), Uprice decimal(18,2) ) insert into tmpStore exec procOne 'St_BFG','SCI019', 'BIO-JVS-30' output select * from tempStore; DROP TABLE tmpStore; END — Harsha, Feb 24 '12 at 11:53
:@Simon -> I am going to pass the parameters from procOne which im going to select from another table in procOne. — Harsha, Feb 24 '12 at 11:58

score 7 · Answer 1 · answered Feb 24 '12 at 11:52

7

use the out parameter like this

CREATE PROCEDURE abc(IN id INTEGER, OUT text VARCHAR(255))
BEGIN 
    SET text = 'asd';
END

you can call this procedure like this:

SET @outvar = '';
CALL abc(1, @outvar);

than the 'asd' will be saved in @outvar

answered Feb 24 '12 at 11:52

silly

7,789
2
24
37

If you think about it, `out` parameters are a very sensible way to handle the problem (forces naming of "returned" results) – bobobobo Jun 04 '13 at 00:13

score 2 · Accepted Answer · answered Feb 24 '12 at 11:57

2

Procedures do not return values, you'll have to declare procOne as a function instead:

CREATE FUNCTION `procOne`(stName varchar(7),fullItemCode varchar(12), fullLedNo varchar(12)) 
RETURNS INTEGER
BEGIN 
  set @x = concat(' 
  SELECT ID, Uprice FROM ',stName,' 
    INTO @var1, @var2 
    WHERE Uprice > 0 
      AND FulItmCode="',fullItemCode,'" 
      AND FullLedgerNo = "',fullLedNo,'" 
     ORDER BY Dat DESC limit 1; 
  '); 
  PREPARE stmt FROM @x; 
      EXECUTE stmt; 
      DEALLOCATE PREPARE stmt; 
  RETURN @var1;
END

Note that a function can only return a single value per call.

answered Feb 24 '12 at 11:57

Johan

74,508
24
191
319

1

To expand on this answer. MySQL stored procedures don't return values directly, but they store output values in variables declared in the `CALL` statement.You then need to `SELECT @varName` to get the value. – codewaggle Dec 20 '12 at 16:54
This is technically not 100% correct. Functions have other limitations [(eg they cannot perform transactions)](http://stackoverflow.com/questions/16969875/error-code-1422-explicit-or-implicit-commit-is-not-allowed-in-stored-function) All he has to do is `SELECT @x` to get the desired result. – bobobobo Jun 06 '13 at 19:51

Simon at The Access Group · Answer 3 · 2012-02-24T12:08:44.767

-1

As mentioned in above comments, I don't fully understand what you are doing. Taking your information above verbatim though, the following would seem to achieve the desired effect.

CREATE PROCEDURE `prcTwo`()
BEGIN
    CALL procOne(@param1, @param2);
END

In your exact procedure, you were missing some semicolons. The below is your prcTwo procedure with errors corrected

CREATE PROCEDURE `prcTwo`()
BEGIN

CREATE TEMPORARY TABLE tmpStore (
  ID int(10),
  Uprice decimal(18,2)
);

INSERT INTO tmpStore CALL procOne(@param1, @param2) ;

SELECT * FROM tempStore; 

DROP TABLE tmpStore;

END

edited Feb 24 '12 at 12:08

answered Feb 24 '12 at 11:55

Simon at The Access Group

6,932
22
45

I goth this error when I tried this ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into tmpStore exec procOne('St_BFG','SCI019', 'BIO-JVS-30') – Harsha Feb 24 '12 at 12:07
Sorry missed that one - you use `CALL` to use a stored procedure, not `EXEC` – Simon at The Access Group Feb 24 '12 at 12:09

how to store returning values of a stored procedure in mysql

3 Answers3