Not getting expected output while using Temp Table or Cursor in MySQL

Question

I'm doing some POC. I've written one stored procedure in MySQL. I'm using MySQLWorkbench for database operations like creating new table, stored procedures, query execution etc. I'm observing some unexpected execution behavior even though code looks correct syntactically and logically.

Here are the points.

Approach 1 -

In first approach, I'm creating temporary table and adding records using INSERT INTO ...SELECT statement with selected columns.

CREATE TEMPORARY TABLE XYZ(....);

INSERT INTO XYZ (....) SELECT (....) FROM ABC WHERE clause;

After doing this, I get null values only in couple of columns of temp table and all other temp table columns gets populated with correct value.

If I run same select statement (for same where clause) in separate SQL tab, I can see correct output even for those couple of columns getting null value in temp table.

If I change above INSERT INTO..SELECT statement with * (all columns), then I get correct output in temp table..

INSERT INTO XYZ  SELECT * FROM ABC WHERE clause;

But don't want all columns. I also tried creating temp table with ENGINE = value. But didn't work. Even I tried all this approach with normal table (without TEMPORARY), but unfortunately

Approach 2 -

In second approach, I modified same stored procedure with cursor instead of temp table. But even in this case cursor is not getting any record(s).

DECLARE cur1 CURSOR FOR SELECT (....) FROM ABC WHERE clause;

If I run same select statement (for same where clause) in separate SQL tab, I can see correct output.

Am I doing something wrong? Can I get any suggestion(s) on this?

Thanks in advance.

Answer 1

I had figured out the problem in Approach #1. The root cause was the variable name used as one of IN parameter. I had used same IN variable name as my table column name. And that is why, I believe, somehow it was over writing actual table values. I changed IN variable name and my stored proc ran correctly.