How can I make a stored procedure to print a specific message if an empty set wa returned by the query?
If you are using MySQL 5.5 or 5.6, you can accomplish this with
Simplified example, inside the body of the procedure...
IF NOT EXISTS (SELECT * FROM users WHERE username = input_username AND password = input_password) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'authentication failed.'; ELSE -- normal code to select and return the username other info to the caller, etc. END IF;
This will throw an exception:
ERROR 1644 (45000): authentication failed.
If you wanted to be more specific, you'd need to declare a variable to use a the
MESSAGE_TEXT and populate it accordingly before calling
SIGNAL only accepts a variable -- not an expression -- as an argument:
DECLARE custom_error TEXT DEFAULT NULL; ... SET custom_error = LEFT(CONCAT('authentication failed for user ',IFNULL(CONCAT("'",input_username,"'"),'NULL')),128); ... SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = custom_error;
Your code would probably take a different approach than
IF ... EXISTS such as selecting
INTO a variable and then testing that variable for null and then either returning it to the called with an unbounded
SELECT or throwing the exception.
The reason for the
LEFT( ...,128) is that this is the maximum length for an argument to
MESSAGE_TEXT. Overrunning it won't hurt anything but you'll end up throwing an error about the invalid argument instead of the error you intended.
For simplicity, the example assumes that the password is in cleartext which obviously wouldn't be the case.