Posted by: prajapatinilesh | January 21, 2008

MySql stored procedure

MySQL example for dynamic SQL stored procedure:

The following example will build a SQL statement with a dynamic WHERE clause. The WHERE clause construct needs to reflect input parameters being passed both when they have a value and when they are NULL. When NULL, the WHERE clause needs to not include that parameter, so it is necessary to do things dynamically.

First, the table definition for this example:

CREATE TABLE `testDynamic` (
`SystemID` int(10) unsigned NOT NULL auto_increment,
`SomeName` varchar(120) NOT NULL,
PRIMARY KEY (`SystemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Put some records in table if you like to test this… And now the stored procedure with dynamic SQL:

DELIMITER $$

DROP PROCEDURE IF EXISTS `TestDynamicSQL`$$

CREATE PROCEDURE `TestDynamicSQL` (
_FromID INT,
_ToID INT)
BEGIN

DECLARE _statement VARCHAR(500);
SET _statement = ‘SELECT * FROM testDynamic ‘;

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;

/* un-comment the line below if you want to also view the
SQL statement that was just constructed */
#SELECT _statement;

SET @statement = _statement;
PREPARE dynquery FROM @statement;
EXECUTE dynquery;
DEALLOCATE PREPARE dynquery;

END$$

DELIMITER ;$$

Now, executing the procedure gives you options. You can pass NULL to parameters or pass values. If you pass NULLs, then you get all records. If you pass values then you get a subset of values.

Examples of valid useage:

call TestDynamicSQL(5, 20);

call TestDynamicSQL(22, NULL);

call TestDynamicSQL(NULL, 10);

call TestDynamicSQL(NULL, NULL);

Example of INVALID useage:

call TestDynamicSQL(20, 5); #invalid because value range overlaps the wrong way, but does not cause execution error, just no rows returned.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: