Problematic prepared statement problem in stored procedure
Posted by:
Bill Phan
Date: April 20, 2013 02:01AM
I'm trying to create a prepared statement for an INSERT that takes its input from a parameterized SELECT statement. All of this is contained within a stored procedure.
Here are the key SQL statements for the stored procedure:
CREATE PROCEDURE twelve_mnth_usr_report(start_month VARCHAR(20), table_name VARCHAR(20), num_iterations INT, choice INT)
BEGIN
DECLARE next_month DATETIME;
DECLARE curr_month DATETIME;
DECLARE last_month DATETIME;
DECLARE counter INT default 0;
SET curr_month=start_month;
SET last_month=DATE_SUB(start_month, INTERVAL 1 MONTH);
SET next_month=ADDDATE(start_month, INTERVAL 1 MONTH);
Here are the SQL statements that comprise the problematic prepared statement. The
problem is evidently w/the use of the declared variable curr_month. I've tried various other permutations to get it to work (i.e. ''',curr_month,''' and even
including curr_month as part of the string) but nothing seems to work, not even
using a string literal for the DATETIME value.
SET @sql_stmt_insrt_mnth_rprt = CONCAT('INSERT INTO Monthly_Report (month,job_user,TotCPU,TotRT,TotGbytes_secs,TotIO,Totmaxvmem) SELECT ',curr_month,',owner,SUM(CPU)/3600 AS "TotCPU",SUM(ru_wallclock)/3600 AS "TotRT",SUM(mem) AS "Gbytes-secs", SUM(io) AS "I/O",SUM(maxvmem) FROM ',table_name,' WHERE (submission_time <> 0) AND (end_time <> 0) AND (start_time < UNIX_TIMESTAMP(''',next_month,''')) AND (((end_time >= UNIX_TIMESTAMP(''',curr_month,''')) AND (start_time >= UNIX_TIMESTAMP(''',last_month,''')) AND (end_time < UNIX_TIMESTAMP(''',next_month,''')) AND ((end_time - UNIX_TIMESTAMP(''',curr_month,''')) >= (UNIX_TIMESTAMP(''',curr_month,''') - start_time))) OR ((start_time >= UNIX_TIMESTAMP(''',curr_month,''')) AND (start_time < UNIX_TIMESTAMP(''',next_month,''')) AND ((end_time - UNIX_TIMESTAMP(''',next_month,''')) < (UNIX_TIMESTAMP(''',next_month,''') - start_time)))) GROUP BY owner ORDER BY "Total RT by owner" DESC;');
PREPARE s1 FROM @sql_stmt_insrt_mnth_rprt;
EXECUTE s1;
DEALLOCATE PREPARE s1;
start_month is a string of the form 2013-03-01 00:00:00