MySQL Forums
Forum List  »  Stored Procedures

Some help w/dynamic SQL query
Posted by: Bill Phan
Date: April 10, 2013 01:08PM

I'm using Server version: 5.0.95 Source distribution on CentOS 5.5.

I have the following partial SQL code for a stored procedure and can't get the dynamic SELECT query to work as it throws a runtime error. I've tried maybe six other permutations of the dynamic SELECT query and none of them work. What I want to do is get DATETIME data from the field file_timestamp in the table stored in TimeStamp_TableName saved into the local variable stored_file_datetime.

DROP PROCEDURE IF EXISTS CHKDB$$

CREATE PROCEDURE CHKDB(IN read_file_date DATETIME, IN file_name VARCHAR(25), OUT newer_file_found INT)
BEGIN

DECLARE stored_file_datetime DATETIME DEFAULT NULL;
DECLARE TimeStamp_TableName VARCHAR(30);

SELECT CONCAT(file_name, '_timestamp') INTO TimeStamp_TableName;

SET @sql_stmt_get_stored_file_dte = CONCAT('SELECT file_timestamp FROM ',TimeStamp_TableName,' INTO stored_file_datetime;');
PREPARE s0 FROM @sql_stmt_get_stored_file_dte;
EXECUTE s0;

the result of running the above stored procedure is: ERROR 1327 (42000): Undeclared variable: stored_file_datetime

Options: ReplyQuote


Subject
Views
Written By
Posted
Some help w/dynamic SQL query
2738
April 10, 2013 01:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.