I am doing some POC. I wrote one stored procedure in MySQL. I use MySQLWorkbench for database operations, such as creating a new table, stored procedures, executing queries, etc. I observe some unexpected behavior during execution, even if the code looks correctly syntactically and logically.
Here are the points.
Approach 1 -
In the first approach, I create a temporary table and add records using the INSERT INTO ... SELECT statement with the selected columns.
CREATE TEMPORARY TABLE XYZ(....); INSERT INTO XYZ (....) SELECT (....) FROM ABC WHERE clause;
After that, I get zero values in only a few columns of the temp table, and all other columns of the temp table are filled with the correct value.
If I run the same select statement (for the same where clause) on a separate SQL tab, I can see the correct output even for those two columns that get a null value in the temp table.
If I changed the INSERT INTO..SELECT statement above with * (all columns), then I get the correct output in the temp table.
INSERT INTO XYZ SELECT * FROM ABC WHERE clause;
But not all columns are needed. I also tried to create a temporary table with an ENGINE = value. But it didn’t work out. Even I tried this whole approach with a regular table (without TEMPORARY), but unfortunately
Approach 2 -
In the second approach, I modified the same stored procedure using the cursor instead of the temp table. But even in this case, the cursor does not receive any records.
DECLARE cur1 CURSOR FOR SELECT (....) FROM ABC WHERE clause;
If I run the same select statement (for the same where clause) on a separate SQL tab, I can see the correct output.
Am I doing something wrong? Can I get any suggestions on this?
Thanks in advance.