Re: Calling stored procedures ... Dynamic SQL
Posted by:
Perry Way
Date: February 11, 2006 12:13PM
Thanks for this example! It sure did short-cut me to a solution for some of what I need to do, but not all. In my SQL Server databases I frequently use Dynamic SQL. Oh, it comes up all over the place, actually. So I'm desparately seeking for a way to make this happen in MySQL too.
After some bumbling around I was able to build a stored procedure that contained Dynamic SQL, but it appears to only work for query EXECUTIONS as a dataset is not returned via a dynamic statement for SELECT queries.
Is there a method of using Dynamic SQL to return a dataset? The following example did not work for me. The "SELECT _statement;" worked and returned the Dynamic SQL string that I had built, but no dataset was returned. :(
Help anyone?
DELIMITER $$;
DROP PROCEDURE IF EXISTS `myhermes`.`TestDynamicSQL`$$
CREATE PROCEDURE `myhermes`.`TestDynamicSQL` (
_FromID INT,
_ToID INT)
BEGIN
DECLARE _statement VARCHAR(500);
SET _statement = 'SELECT * FROM hwfprocs ';
IF (_FromID IS NOT NULL) OR (_ToID IS NOT NULL) THEN
SET _statement = CONCAT(_statement, 'WHERE ');
IF (_FromID IS NOT NULL) THEN
SET _statement = CONCAT(_statement, '(SystemID >= ', _FromID, ') ');
END IF;
IF (_ToID IS NOT NULL) THEN
IF (_FromID IS NOT NULL) THEN
SET _statement = CONCAT(_statement, 'AND ');
END IF;
SET _statement = CONCAT(_statement, '(SystemID <= ', _ToID, ') ');
END IF;
END IF;
SELECT _statement;
PREPARE dynquery FROM @statement;
EXECUTE dynquery;
DEALLOCATE PREPARE dynquery;
END$$
DELIMITER ;$$