CODEWITHSUNDEEP
mysqlsql
Dev1ce
Dev1ce

Reputation: 5944

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.

Upvotes: 0

Views: 220

Answers (1)

fancyPants
fancyPants

Reputation: 51878

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

Read more about the differences in the manual:

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 ;

Upvotes: 1

Related Questions