CODEWITHSUNDEEP
mysqlstored-procedures
Michael Mior
Michael Mior

Reputation: 28752

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 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!

Upvotes: 2

Views: 3518

Answers (1)

Mark Byers
Mark Byers

Reputation: 838716

Try this:

UPDATE yourtable
SET value = val1
WHERE cond = val2 OR val2 IS NULL

Upvotes: 7

Related Questions