Problem creating Stored Procedure in MySQL Administrator

andrewhiggs · 07-22-2010, 07:43 AM

Can anyone tell me what is wrong with this SQL to create a stored procedure in MySQL please?

Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckStatus`(
        IN start_date DATE, 
        IN end_date DATE)
BEGIN

      CREATE TEMPORARY TABLE temp_sales(
        id INTEGER, 
        dated DATE, 
        total DECIMAL(12,2));
      CREATE TEMPORARY TABLE temp_customer_counts(
        id INTEGER, 
        dated DATE, 
        total DECIMAL(12,2));
      
      SELECT s.* INTO temp_sales FROM
        (
          SELECT store_id, dated, sum(value) FROM sales 
            WHERE dated >= start_date 
            AND dated <= end_date
            GROUP BY store_id, dated
        ) AS s;

      SELECT c.* INTO temp_customer_counts FROM
        (
          SELECT store_id, dated, sum(value) FROM customer_counts 
            WHERE dated >= start_date 
            AND dated <= end_date
            GROUP BY store_id, dated
        ) AS c;

      SELECT ts.store_id, ts.date, ts.total AS 'Sales Total', tc.total AS 'Customer Sales Total' FROM temp_sales ts
        JOIN temp_customer_counts tc ON ts.store_id = tc.store_id AND ts.dated = tc.dated;
      
      DROP TABLE temp_sales;
      DROP TABLE temp_customer_counts;

END

To me the syntax is correct. It complains with error 1327 'Undeclared variable: temp_sales'. What I don't understand is why I would need to declare a temporary table's name as a variable. Am I missing something here?

Assistance as always is greatly appreciated.

Thanks.

andrewhiggs · 07-23-2010, 03:27 AM

Okay. Got Administrator to compile (execute) the Procedure by changing it slightly.

Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckStatus`(
        IN start_date DATE, 
        IN end_date DATE)
BEGIN

      CREATE TEMPORARY TABLE temp_sales(
        id INTEGER, 
        dated DATE, 
        total DECIMAL(12,2));
      CREATE TEMPORARY TABLE temp_customer_counts(
        id INTEGER, 
        dated DATE, 
        total DECIMAL(12,2));
      
      SELECT s.* INTO @temp_sales FROM
        (
          SELECT store_id, dated, sum(value) FROM sales 
            WHERE dated >= start_date 
            AND dated <= end_date
            GROUP BY store_id, dated
        ) AS s;

      SELECT c.* INTO @temp_customer_counts FROM
        (
          SELECT store_id, dated, sum(value) FROM customer_counts 
            WHERE dated >= start_date 
            AND dated <= end_date
            GROUP BY store_id, dated
        ) AS c;

      SELECT ts.store_id, ts.date, ts.total AS 'Sales Total', tc.total AS 'Customer Sales Total' FROM temp_sales ts
        JOIN temp_customer_counts tc ON ts.store_id = tc.store_id AND ts.dated = tc.dated;
      
      DROP TABLE temp_sales;
      DROP TABLE temp_customer_counts;

END

The problem is that now when I try to call the procedure (using query browser) it reports error 1222 'The used SELECT statements have a different number of columns'. I can't see that anywhere.

What am I missing?

Regards
Andrew Higgs