how to store returning values of a stored procedure in mysql


Problem :

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


Solution :

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.


Mysql Tutorials

Mysql Howto..

How can i generate a 6 digit number in php?

mysql how to merge /join/complete information from 1 rows to other row from the same table

How to fetch NULL values in Mysql through joins?

How to dynamically generate MYSQL UPDATE statement based on defined variables from HTML FORM

How to get the returned rows after executing a PHP/MYSQL prepared statement? [closed]

how to check null or not if mysql datatype is timestamp with a default value of “0000-00-00 00:00”?

How to get the sum of the sum with one mysql query

In MySql how do I add missing decimals to the currency column?

How to insert data in the nested set model(MySQL);

How to normalize or design my MySQL database

How to format this MySQL query?

mysql how to restore data for part of a table

How to update a Single Character in a mysql table which contain number of Fields

How to insert transaction into table in mysql keeping it unique for every user

How to Query Mysql based on GPS latitude and longitute columns based on relative distance

Use AJAX dropdown field search and add to array to show results live [closed]

how to inject mock testing hapi with Server.inject

how can i change this mysql code to pdo?

Mysql - how to allow user to login?

how to show rows as column in mysql view table [closed]

PHP: How to foreach in foreach with condition?

MySQL - How to select all from table “jobs” but exclude results where “bidderid” exists in table “bids” (some ref. is table “bids” has “bidjobid”)

How to store out of default range dates in MySQL database?

How to join values from a nested table value from mysql database

How to create a mysql database and tables dynamically using symfony and doctrine

how to get the sum of a specific user from two tables?

How to store tweets from Twitter Streaming API in MySQL with SSH closed

how to display the database records with MySQL Query

How to update MYSQL field without refreshing page in PHP?

MySQL how to avoid duplicate row