How can I make a stored procedure to print a specific message if an empty set wa returned by the query?

  • If an empy set was returned by which query are you referring to? You can generate arbitrary record sets, exceptions, and warnings from stored procedures, and one of those things is probably what you mean by "print" ... so please be more specific about specifically what you are trying to do and when, and under what condition. – Michael - sqlbot Sep 1 '13 at 15:49
  • We have an authentication database so if we make a select and the user/password is wrong or doesn't exist we want that case to be handled on the level of the database not the application so instead of handling empty set exception using the application we would like to do it using MySQL – Ahmad Aabed Sep 2 '13 at 10:04

If you are using MySQL 5.5 or 5.6, you can accomplish this with SIGNAL.

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.';
  -- normal code to select and return the username other info to the caller, etc.

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 because SIGNAL only accepts a variable -- not an expression -- as an argument:

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.