My SQL dynamic query is executed and gets output to a variable in a stored procedure

Question

I am creating a dynamic query in a My sql stored procedure. I want to get the result of this query in the out parameter. How to do it?

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 request1 output to OUT parameter ( numOfRecords ) ??

Answer 1

Take 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 | +---------+---------+ 

Answer 2

 select count(*) into @numOfRecords from .... 

You specified a variable in a stored procedure

I hope I understand your question.