Re: Calling stored procedures ... Dynamic SQL

[Michael Joss]

Hi Perry,

Might just be that particular example that you chose but in that case you could simply do the same thing using the CASE statement rather than going to the trouble of using dynamic SQL.

DELIMITER $$;

DROP PROCEDURE IF EXISTS `TestDynamicSQL`$$

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

SELECT *
FROM testDynamic
WHERE
systemID >= CASE WHEN @fromID IS NULL THEN systemID ELSE @fromID END
AND systemID <= CASE WHEN @toID IS NULL THEN systemID ELSE @toID END end;

END$$

DELIMITER ;$$

would return exactly the same thing (probably quicker) and certainly a lot easier to follow? In MSSQL you could even use ISNULL() instead of the whole case statement. Of course a simple > or < rather than >= or <= would make your life more difficult (though not impossible) using this method.

Hope thats helpful

Cheers

MJ