How To Use IF Statement and Local Variables In Stored Procedures

Question

I'm testing this in MyPhpAdmin on "10.5.24-MariaDB-cll-lve", I can not define SET statements such as "SET @x = 1;" (from the manual),they don't fail but the following statementis flagged as failing. I have also tried DECLARE.

If I put a begin/end around the procedure then it will save but fail to execute telling me that it fails with "... near NULL at line 1".

The following shows a simple cut down test case of trying to declare variables (a delimiter issue of some type?):

#BEGIN
#   DELIMITER $$
#    $$
    #SET @try1 = 1$$
    #SET @try1 = 1, @try2 = 2, @try3 = 3$$
    #SET @try4 = 4, @try5 = 5, @try6 = 6$$ (failed on start of this line)
    
    
    SET @try1 = 1;
    SET @try2 = 2;
    SET @try3 = 3;
    
#   DELIMITER ;
#END

If I use the SQL to create the stored proc shown here, it works, if I add another declare/set like the following it fails to create complaining about my second set!:

   DECLARE total_value INT;
       SET total_value = 50;
       
   DECLARE total_value2 INT;
       SET total_value2 = 50;

The following shows what I'm actually trying to do:

I am using dynamic SQL here as IF statements with static were also failing.

I have exported that and added it here:

DELIMITER $$
CREATE DEFINER=`wcipporg`@`localhost`
    PROCEDURE `BBBBBBBBB`
        (IN `SinceDate` DATE,
         IN `Totals` VARCHAR(10))
BEGIN

DECLARE selectP1 VARCHAR(999);
    SET selectP1 = 'SELECT SinceDate AS `Sales Since`,';
    
IF Totals = 'N' THEN 
   SET @selectP2 = 'wp_posts.post_title AS Plant,
                    wp_terms.name AS Container,';
ELSE    
   SET @selectP2 = "COALESCE(wp_posts.post_title, '### All Plants ###')      AS `Plant Name`,
                    COALESCE(wp_terms.name,       '### All Containers ###')  AS `Container`,";
END IF;        
SET @selectP3 = 'SUM(sold_plants.how_many) AS Total
                 FROM wcipporg_Bungalook.sale AS sale
                 INNER JOIN wcipporg_Bungalook.sold_plants AS sold_plants
                         ON sale.txn_id = sold_plants.txn_id
                 INNER JOIN wcipporg_wp596.wpi2_posts AS wp_posts
                         ON sold_plants.plant_id = wp_posts.ID
                 INNER JOIN wcipporg_wp596.wpi2_terms AS wp_terms
                         ON sold_plants.container_id = wp_terms.term_id
                 WHERE sale.txn_time >= SinceDate';
IF Totals = 'N' THEN 
   SET @selectP3 = 'GROUP BY wp_posts.post_title ASC, wp_terms.name';
ELSE    
   SET @selectP3 = 'GROUP BY wp_posts.post_title ASC, wp_terms.name WITH ROLLUP';
END IF;


SET @query = CONCAT(@selectP1, @selectP2, @selectP3, @selectP4);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

I've tried all sorts of variations, googling and the manual, the error messages say the manual for my version but it doesn't say how to find that specific manual, I am using: https://mariadb.com/kb/en/documentation/

Answer 1

Some things I now believe to be true for mariadb stored procedures:

1. Best to use BEGIN/END to enclose stored procedure (whether required or not).
2. @variables do work. I thought, converting passed IN parameters to @variables would make testing outside of the procedure much easier (but IF not supported).
3. "DECLARE" STATEMENTS, if used, must all appear first (can't interleave with associated "SET" statements).
4. Error 1064 Near NULL at line 1 occurs if a variable hasn't been intialised, a mistake meant that "selectP4" wasn't being set.

Here is my final working code

BEGIN
SET @InStoredProc = 'Y';
    IF  @InStoredProc = 'N' THEN 
        SET @SinceDate = '2020-01-01';
        SET @Totals    = 'Y';
    ELSE
        SET @SinceDate = SinceDate;
        SET @Totals    = Totals;
    END IF;

SET @AllPlants     = '### All Plants ###';
SET @AllContainers = '### All Containers ###';

SET @SelectP1 = CONCAT('SELECT "', @SinceDate, '" AS `@Sales Since`,');

IF @Totals = 'N' THEN 
   SET @SelectP2 = 'wp_posts.post_title AS Plant,
                    wp_terms.name AS Container,';
ELSE    
   SET @SelectP2 = "COALESCE(wp_posts.post_title, @AllPlants)      AS `Plant Name`,
                    COALESCE(wp_terms.name,       @AllContainers)  AS `Container`,";
END IF;        

SET @SelectP3 = 'SUM(sold_plants.how_many) AS Total
                FROM wcipporg_Bungalook.sale AS sale
                INNER JOIN wcipporg_Bungalook.sold_plants AS sold_plants
                        ON sale.txn_id = sold_plants.txn_id
                INNER JOIN wcipporg_wp596.wpi2_posts AS wp_posts
                        ON sold_plants.plant_id = wp_posts.ID
                INNER JOIN wcipporg_wp596.wpi2_terms AS wp_terms
                        ON sold_plants.container_id = wp_terms.term_id
                WHERE sale.txn_time >= "';
SET @SelectP3 = CONCAT(@SelectP3, @SinceDate, '"');

IF @Totals = 'N' THEN 
   SET @SelectP4 = ' GROUP BY wp_posts.post_title ASC, wp_terms.name';
ELSE    
   SET @SelectP4 = ' GROUP BY wp_posts.post_title ASC, wp_terms.name WITH ROLLUP';
END IF;


SET @Query = CONCAT(@SelectP1, @SelectP2, @SelectP3, @SelectP4);
#SET OUTPUT = @Query;
    PREPARE stmt FROM @Query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END