MonkeyClicker

I have a stored procedure with a large select statement in it to return a specific data set based on the input parameter. I want to be able to return a general data set, all records, if no parameter is passed in. I know I can just cut and paste the whole sql statement in an if-then-else block but I was looking for a more eloquent solution.

Tou can accomplish this quite easily by building your SQL statment into a string variable and then executing the text SQL statement with the EXECUTE command.

Code: Select all

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


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.