Stored procedure to handle empty sets

Question

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

• Michael - sqlbot: 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.
• Ahmad Aabed: 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

Answer 1

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.';
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 because 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.