Defining user variables in stored procedures / routines for MySQL 9.0

Question

According to the MySQL 8.0 deprecations notes, starting MySQL 9.0 the definition of user variables with DECLARE (e.g. DECLARE studentID INT) will be deprecated:

Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13. This functionality is subject to removal in MySQL 9.0.

Already nowadays MySQL 8.0.25 raises a warning:

1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: SET variable=expression, ..., or SELECT expression(s) INTO variables(s).

Therefore, I would like to understand how to properly replace DECLARE with SET. Let say, I have the following variables declared:

DECLARE cursor_studentID INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_i CURSOR FOR SELECT courseID FROM tblCoursesToCopy;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

And now I want to make these declaration MySQL 9.0 compatible. In case of DECLARE cursor_studentID INT; everything is relatively obvious:

DECLARE cursor_studentID INT; → SET @cursor_studentID;

The real obstacle is the last one case:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

How to replace it with SET-based approach?

Answer 1

The deprecation warnings are not clear, and are easily misunderstood.

The DECLARE syntax is not deprecated. I think the authors of the release notes do not consider that a method of "setting" a variable. Admittedly, it does set an initial value for a declared variable, by using the optional DEFAULT clause. But I still interpret it as a statement that is not counted as one that is deprecated.

What is deprecated is setting variables as a "side-effects" in expressions like this:

SELECT @row := @row+1, ...

That technique was a common workaround for MySQL's lack of window functions in releases before 8.0. But it's nonstandard syntax and is generally error-prone and tricky to use. Now that MySQL supports standard SQL window functions and common table expressions, you can write queries much like you would write in other popular implementations of SQL. The above example would be:

SELECT ROW_NUMBER() OVER (), ...

The other use of := assignments was to save expressions from a query's select-list into a user-defined variable. MySQL provides the SELECT ... INTO syntax for this usage. This has been supported for a long time, before version 8.0.

We have not heard any announcement of a change to the DECLARE syntax. That does not need to change.

---

Re your comments:

Instead of SELECT @myVar := somevalue ..., you can use either of the following forms:

SELECT someValue INTO @myVar ...

SET @myVar = (SELECT someValue FROM ...);

Note the latter form does not allow you to set more than one variable per query. The former allows you to set multiple variables like this:

SELECT someValue, otherValue INTO @myVar1, @myVal2 ...

Read the documentation on SELECT ... INTO that I linked to above.