My SQL Dynamic query execute and get ouput into a variable in stored procedure
47,560
Solution 1
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 |
+---------+---------+
Solution 2
select count(*) into @numOfRecords from ....
You have do declare the variable within stored procedure
I hope I've understood your question.
Related videos on Youtube
Comments
-
Sency about 4 years
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
) ?? -
er.irfankhan11 over 6 years@Devart what if getting multiple records, how to store in temp table?