How to alter the default value of a column using a procedure

Question

Here is what my current SQL looks like:

DELIMITER $$
CREATE PROCEDURE updateDefaultUserRole(
    IN rid_in INT
) BEGIN
    ALTER TABLE _users
    MODIFY rid INT(255) NOT NULL DEFAULT rid_in; -- Modify the columns default value
    UPDATE _users SET rid = rid_in WHERE rid < rid_in; -- Update all entries lower than the role ID.
END $$
DELIMITER ;

Here is what my database _users table looks like:

CREATE TABLE `_users` (
  `uid` int(255) NOT NULL,
  `forname` varchar(40) NOT NULL,
  `surname` varchar(40) NOT NULL,
  `email` varchar(120) NOT NULL,
  `hash` varchar(60) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rid` int(255) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `_users`
  ADD PRIMARY KEY (`uid`),
  ADD KEY `rid` (`rid`);
  MODIFY `uid` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

I am receiving this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rid_in; -- Modify the columns default value

Which links to this line:

ALTER TABLE _users
MODIFY rid INT(255) NOT NULL DEFAULT rid_in;

If I change this, ignoring the rid_in value:

ALTER TABLE _users
MODIFY rid INT(255) NOT NULL DEFAULT '1';

It works fine, how can I bind the rid_in input to the query?

My expected output is to be able to use to change the default user role value and update all the rows with the old user value to the new one:

CALL updateDefaultUserRole(@someInt)

To be able to update the default value for each user.

Answer 1

You need to use Dynamic SQL here, as Default clause in the Alter Table will not be able to resolve the variable value:

DELIMITER $$
CREATE PROCEDURE updateDefaultUserRole(
    IN rid_in INT
) BEGIN

    -- generate the query string for Alter Table
    SET @alter_query_str = CONCAT('ALTER TABLE _users
                                   MODIFY rid INT(255) NOT NULL 
                                   DEFAULT ', 
                                  rid_in); -- Modify the columns default value
    -- prepare the query
    PREPARE stmt FROM @alter_query_str;
    -- execute the query
    EXECUTE stmt;
    -- deallocate the query
    DEALLOCATE PREPARE stmt;

    UPDATE _users SET rid = rid_in 
    WHERE rid < rid_in; -- Update all entries lower than the role ID.

END $$
DELIMITER ;