mysqlstored-proceduressqldynamic-queries

My SQL Dynamic query execute and get ouput into a variable in stored procedure


I generate a dynamic query in My sql Stored procedure. I wanna get the result of this query into a out parameter. How to do this ?

CREATE PROCEDURE 'searchInvoice'
(
  OUT numOfRecords INT
)
BEGIN
  DECLARE query1 TEXT; 
  DECLARE query2 TEXT; 

 SET query1 = 'SELECT COUNT(*) bla bla bla.....'; 
 // Query1 to select the count of matching tuples..

 SET query2 = 'SELECT * from bla bla bla....';
 // Query2 to select original records...

 // later part of this both queries generate dynamically according to some IN parameters..

 // now I wanna assign the output of the query1 into numOfRecords 
 // and I wanna execute the query2 as well.. like this

    SET @Sql = query2;        
    PREPARE STMT FROM @Sql; 
    EXECUTE STMT; 
    DEALLOCATE PREPARE STMT;

 // output of the query2 can be read in PHP

END

How to get the output of the query1 into OUT parameter(numOfRecords ) ??


Solution

  • Have a look at this example -

    CREATE TABLE table1(
      column1 VARCHAR(255) DEFAULT NULL,
      column2 VARCHAR(255) DEFAULT NULL,
      column3 VARCHAR(255) DEFAULT NULL
    );
    
    INSERT INTO table1 VALUES 
      ('1', 'value1', 'value2'),
      ('2', 'value3', 'value4');
    
    DELIMITER $$
    CREATE PROCEDURE procedure1(IN Param1 VARCHAR(255), OUT Param2 VARCHAR(255), OUT Param3 VARCHAR(255))
    BEGIN
      SET @c2 = '';
      SET @c3 = '';
      SET @query = 'SELECT column2, column3 INTO @c2, @c3 FROM table1 WHERE column1 = ?';
      PREPARE stmt FROM @query;
      SET @c1 = Param1;
      EXECUTE stmt USING @c1;
      DEALLOCATE PREPARE stmt;
      SET Param2 = @c2;
      SET Param3 = @c3;
    END$$
    DELIMITER ;
    
    -- Call procedure and use variables
    SET @Param1 = 2;
    SET @Param2 = '';
    SET @Param3 = '';
    CALL procedure1(@Param1, @Param2, @Param3);
    SELECT @Param2, @Param3;
    +---------+---------+
    | @Param2 | @Param3 |
    +---------+---------+
    | value3  | value4  |
    +---------+---------+