MySQL SELECT returns NULL for NOT NULL column in stored procedure

Question

I have a stored procedure in which I'm trying to loop over a number of IDs in a table and insert them into another table... problem is the ID turns out as NULL in the loop.

For debugging purposes I have created a table called test, it has two columns named var_name and value. I also made a stored procedure like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcedure`(@parent INT(11))
BEGIN
    INSERT INTO `test`
        (`var_name`, `value`)
    VALUES
        ('parent', @parent);

    INSERT INTO test (`var_name`, `value`)
        SELECT 'id', `id`
        FROM `mytable`
        WHERE `parent` = @parent;
END

The table mytable has a lot of columns but id is the primary key and obviously NOT NULL, parent allows NULL. The id, parent and value columns are all INT(11).

The following statement:

CALL myProcedure(1);

Produces the following result in test:

+----------+-------+
| var_name | value |
+----------+-------+
| 'parent' | 1     |
| 'id'     | NULL  |
| 'id'     | NULL  |
| 'id'     | NULL  |
| 'id'     | NULL  |
| 'id'     | NULL  |
| 'id'     | NULL  |
+----------+-------+

The number of 'id' rows match the number of rows in mytable with parent = 1, but value is always NULL. Running the following query:

SELECT `id` FROM `mytable` WHERE `parent` = 1;

Produces the expected result:

+----+
| id |
+----+
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |
+----+

What's going on here?

Abhik Chakraborty · Accepted Answer · 2014-09-16 10:23:12Z

1

Not quite sure what is wrong with the given procedure but I tried creating one similar to yours and it worked pretty well. Here what I did

mysql> create table test (var_name varchar(100),value int);
Query OK, 0 rows affected (0.10 sec)

mysql> create table mytable (id int, parent int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into mytable values (2,1),(3,1),(4,1),(5,1),(6,1),(7,1);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

Then added the following procedure

delimiter //
CREATE PROCEDURE myProcedure(parent INT(11))
BEGIN
declare parent_id int ;
set @parent_id := parent ;

    INSERT INTO `test`
        (`var_name`, `value`)
    VALUES
        ('parent', @parent_id);

    INSERT INTO test (`var_name`, `value`)
        SELECT 'id', `id`
        FROM `mytable`
        WHERE `parent` = @parent_id;
END; //

mysql> CALL myProcedure(1);
Query OK, 6 rows affected (0.05 sec)

mysql> select * from test ;
+----------+-------+
| var_name | value |
+----------+-------+
| parent   |     1 |
| id       |     2 |
| id       |     3 |
| id       |     4 |
| id       |     5 |
| id       |     6 |
| id       |     7 |
+----------+-------+
7 rows in set (0.00 sec)

Only thing I changed is used a variable inside the procedure to hold the param value and use it in the query.

answered Sep 16, 2014 at 10:23

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

moggizx Over a year ago

Thanks... I actually lied when I said my procedure looked like it did. In reality I had inserted that part into my loop procedure which declared some variables at the top... including: DECLARE id INT... I didn't realize a variable could possibly replace a column value if the column was referred to with backticks. Your answer made me try making the procedure exactly like I declared it in the question. Of course, I knew the answer would be something stupid like this :/

wchiquito Over a year ago

In this case I see no need to use 9.4 User-Defined Variables (@parent_id), can be used directly: DECLARE parent_id INT DEFAULT parent;

Abhik Chakraborty Over a year ago

Yes default could be used as mentioned by @wchiquito

moggizx Over a year ago

What is the purpose of declaring a new variable to store the value in instead of just using the one that already exists..?

Abhik Chakraborty Over a year ago

ideally you do not need for small procedures, but for big once its a good practice to have a variables declared where some calculations are involved so that you do not mess up the input data by doing some calculation on it and then using the variable again somewhere else. It also meaningful if your input value is changed by some calculation then use a variable and do the calculation leaving the input intact

moggizx · Accepted Answer · 2014-09-16 12:41:30Z

0

I lied a little bit in the question. My real stored procedure contained the portion I posted at the beginning, but after a couple of DECLARE, including:

DECLARE id INT;

which replaced the value of id in mytable...

answered Sep 16, 2014 at 12:41

moggizx

4761 gold badge5 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT returns NULL for NOT NULL column in stored procedure

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related