Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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