MySQL stored procedure returns null

Question

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried

call NewProc('tableName','colName')

it returned

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1". The error may occur in the "set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);

but I have no clue why it's the problem.

CREATE DEFINER = root@% PROCEDURE NewProc (IN tn VARCHAR(20), IN mc VARCHAR(20))
BEGIN 
 DECLARE j INT;
 DECLARE tab1 VARCHAR(20);
 DECLARE col1 VARCHAR(20);
 DECLARE stmt VARCHAR(1000);
 SET tab1:=tn;
 SET col1:=mc;
 set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);
 PREPARE stmt FROM @sql0;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET j=1;
WHILE j<@i DO 
 SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656' where id=",j);
 PREPARE stmt FROM @sql1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET j=j+1;
END WHILE;
END;

Answer 1

Most likely the issue is with the first dynamic query, the one you are storing into @sql0:

set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);

You are concatenating the value of @i into the query, but @i has not been defined yet at this point – so it is null. Concatenating a null with other string literals gives you a null as well. So @sql0 ends up to be a null and that is what you are attempting to execute subsequently and getting the error mentioned.

My guess is you meant the @i to be part of the dynamic query, so it should probably go like this:

set @sql0=CONCAT('select count(*) into @i from ',tab1);

that is, you need to concatenate the name of @i, not the value. That way the dynamic query will be storing the result of count(*) into @i.

Note, though, that you may also need to declare the variable explicitly before the dynamic query. Otherwise the dynamic query will probably implicitly declare the variable at its own, nested, level, and the variable will go out of scope once the query is completed, and so the @i reference further in your query will still evaluate to null.

• cachekai: Andriy, thank you! You're right, after posting this question, I tried the exact way you pointed out and it succeed but I was still not clear why it happend. You just made a great explaination on it.

Answer 2

Why write a loop? Skip the count and the loop, simply perform this once:

SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656'");

• cachekai: Thank you, James! I made a very low-level mistake on it at some point.