[ACCEPTED]-My SQL Dynamic query execute and get ouput into a variable in stored procedure-dynamic-queries

Accepted answer

Score: 30

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

Score: 3

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

You have do declare the variable within 2 stored procedure

I hope I've understood your 1 question.

Source: stackoverflow.com

MySQL stored procedure that accepts string with multiple parameters

Dropping procedure which already exists?

Error Code 1046: No database Selected

Iterate through JSON in MySQL query

MySql, split a string and insert into table

Calling stored procedure with Out parameter using PDO

Mysql nested while loop in a Stored Procedure

Call a stored procedure from the DECLARE statement when using cursors in MySQL

Dynamic table names in stored procedure function

syntax error on DECLARE CURSOR FOR

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.