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!

1

1 Answer

Try this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Sarah Jenkins

Sarah Jenkins

Senior Technology Editor & AI Specialist

Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.

Share this article
Twitter Facebook Pinterest