How to declare a variable holding select query result in a stored procedure?

I have a MySql stored procedure in which I have a variable which will hold the result of a select query.

But I am unable to use the variable, as I believe one must declare variables with their data types in a stored procedure.

This is a snippet of what I’m trying to do:

CREATE DEFINER=`root`@`localhost` PROCEDURE `backups`()
BEGIN
    DECLARE snapshot_year INT;
    DECLARE partition_year VARCHAR(17);
    #DECLARE isPartionPresent;

    SET snapshot_year := DATE_FORMAT(NOW(),'%Y');
    SET partition_year := CONCAT('backups',snapshot_year);

    SET isPartionPresent := (SELECT PARTITION_NAME
        FROM
            INFORMATION_SCHEMA.PARTITIONS
        WHERE
            TABLE_NAME = 'backups'
            AND PARTITION_NAME = @partition_year);

    IF @isPartionPresent IS NOT NULL THEN
        #Do something
    ELSE
        #Do something
    ENDIF
END

I’m getting a syntax error with isPartionPresent variable.

Answer

You’re mixing local variables with user defined variables. There’s a difference between the two.

Read more about the differences in the manual:

• https://dev.mysql.com/doc/refman/8.0/en/user-variables.html
• https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html

Just decide on which variable type you want to use. Your procedure should work with this for example:

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `backups`()
BEGIN
    DECLARE snapshot_year INT;
    DECLARE partition_year VARCHAR(17);
    DECLARE isPartionPresent varchar(64);

    SET snapshot_year := DATE_FORMAT(NOW(),'%Y');
    SET partition_year := CONCAT('backups',snapshot_year);

    SET isPartionPresent := (SELECT PARTITION_NAME
        FROM
            INFORMATION_SCHEMA.PARTITIONS
        WHERE
            TABLE_NAME = 'backups'
            AND PARTITION_NAME = partition_year);

    IF isPartionPresent IS NOT NULL THEN
        -- do something
        select now(); -- dummy statement to avoid syntax error
    ELSE
        -- do something
        select now(); -- dummy statement to avoid syntax error
    END IF;
END$$
delimiter ;