Conditional Where Clause in Mysql Stored Procedure
Below Is a Simplified Example of a Stored Procedure I've Created. Delimiter // Create Procedure Test (In Val1 Int, in Val2 Int) Begin Update Table Set...
Below is a simplified example of a stored procedure I've created.
DELIMITER //
CREATE PROCEDURE test (IN val1 INT, IN val2 INT)
BEGIN
UPDATE TABLE SET value=val1 WHERE cond=val2;
END //
DELIMITER ;
Now, suppose I want to make the condition optional. I could do this by setting val2 to NULL when calling the procedure and then using an IF statement to use the condition if the value is not NULL. (In my case, it's safe to assume that cond=NULL is not a valid condition to check for.)
This works, but the UPDATE query I have is much more complicated and it seems unfortunate to duplicate it just to make the condition optional. I could dynamically construct the query string and pass to EXECUTE, but that doesn't feel right either and I lose syntax checking.
Any suggestions? Thanks!
1 Answer
Try this:
UPDATE yourtable
SET value = val1
WHERE cond = val2 OR val2 IS NULL