MySQL Forums
Forum List  »  Microsoft SQL Server

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 ;$$

Options: ReplyQuote


Subject
Written By
Posted
Re: Calling stored procedures ... Dynamic SQL
February 11, 2006 12:13PM


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.