La mia query SQL dynamic esegue e ottiene output in una variabile in stored procedure

Genero una query dynamic nella mia stored procedure sql. Voglio ottenere il risultato di questa query in un parametro out. Come fare questo ?

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 

Come ottenere l’output di query1 nel parametro OUT ( numOfRecords ) ??

Dai un’occhiata a questo esempio:

 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 | +---------+---------+ 
 select count(*) into @numOfRecords from .... 

Hai dichiarato la variabile all’interno della stored procedure

Spero di aver capito la tua domanda.