I would like to create a stored procedure which updates either all fields in a table or just a few of them according to parameters passed to it.
How do I create a stored procedure that accepts optional parameters?
0
Add a comment
|
2 Answers
Optional Parameters are not yet supported on MySQL. I'm suggesting that you pass null value in your parameter and inside your stored procedure has an IF statement.
DELIMITER $$
CREATE PROCEDURE procName
(IN param VARCHAR(25))
BEGIN
IF param IS NULL THEN
-- statements ;
ELSE commands
-- statements ;
END IF;
END$$
DELIMITER ;
1 Comment
Kamran Shahid
As Mysql is now virtually owned by Oracle. We will not expecting to be implemented it in future as well.
An special case is when the parameter cant' be NULL, i.e. because is a key. I use a trick for these case: I set the parameter to -1:
CREATE PROCEDURE procCreate
(IN id_cosa INT(11))
BEGIN
IF id_cosa != -1 THEN
~~(your code here)~~
END IF
END
