How to put "EXECUTE statement;" result into a variable so that I can get the result using simple SELECT command whenever I need it. The result will contain random number of column based on user inputs. So, what I want is something like:
SET @RES := EXECUTE stmt;
and then I may apply-
SELECT @RES;
On a second thought, I want the result in-
WITH res_set1 AS (
EXECUTE stmt;
)
SELECT * FROM res_set1;
Thanks in advance.
You should use variables inside your prepared statement.
You will have to structure your prepared statement so that it will give you one row at a time, for example:
SET @sql='SELECT col1, col2 FROM test WHERE id=3 INTO @v1, @v2';
PREPARE stmt FROM @sql;
EXECUTE stmt;
After executing, you will have values from col1 and col2 in variables @v1 and @v2
To process multiple rows, use your prepared statement in a loop:
SET @sql='SELECT col1, col2 FROM test WHERE id=? INTO @v1, @v2';
PREPARE stmt FROM @sql;
SET @idx = 1;
REPEAT
EXECUTE stmt USING @idx;
SET @idx = @idx + 1;
UNTIL @idx > 6 END REPEAT;
