Mysql – Stored procedure to handle empty sets

MySQLstored-procedures

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

Best Answer

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.

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;

Sql-server – Heterogeneous queries error using stored procedure

The message mentions two settings. The first setting, ansi_nulls, is remembered from when the stored procedure was created. Make sure it's on when you run create procedure:

set ansi_nulls on
go
create procedure YourProc as ...

The second setting, ansi_warnings, should be enabled on the connection that calls the procedure:

set ansi_warnings on 
exec YourProc

Or alternatively, set ansi_warnings inside the procedure:

create procedure YourProc 
as
set ansi_warnings on
...

For more information see this MSDN article:

Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.

Best Answer

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

Sql-server – Heterogeneous queries error using stored procedure

Related Question