How to configure error message in MySQL stored function

This is probably a trivial problem for most of you ... The function I created in MySQL is stored below:

DROP PROCEDURE IF EXISTS teamSize;
DELIMITER //

CREATE PROCEDURE teamSize(IN teamName VARCHAR(50), IN teamYear INT(4), OUT noOfPlayers INT)
BEGIN
    IF teamName IN (SELECT teamName FROM team) THEN
        SELECT COUNT(playerID) INTO noOfPlayers 
        FROM team
        JOIN teamAllocation
        ON teamAllocation.teamID = team.teamID
        WHERE team.teamName = teamName 
        AND team.teamYear = teamYear;
    ELSE
        SELECT "That team does not exist in the database" AS "Error Message";
    END IF;
END //

DELIMITER ;

I made this function to calculate the size of a given team for a given year. The team name and team year are analyzed as parameter values. For this function, I want a custom error message to be displayed if the name of the command that the user entered does not exist in the database.

For example: I am typing a command that exists. I get the following results as I expected:

mysql> CALL teamSize("U11 Orange", 2012, @noOfPlayers);
--------------
CALL teamSize("U11 Orange", 2012, @noOfPlayers)
--------------

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @noOfPlayers;
--------------
SELECT @noOfPlayers
--------------

+--------------+
| @noOfPlayers |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql>

... But if I type a command that, as I know, does not exist in the database, I get the following:

mysql> CALL teamSize("jkgefkrkvbey", 2012, @teamSize);
--------------
CALL teamSize("jkgefkrkvbey", 2012, @teamSize)
--------------

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @teamSize;
--------------
SELECT @teamSize
--------------

+-----------+
| @teamSize |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql>

What I want for the case when the command name parsed in the function does not exist looks something like this:

+------------------------------------------+
| Error Message                            |
+------------------------------------------+
| That team does not exist in the database |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>

- - , , .

!

+3
1

, teamName. teamName , :

SELECT teamName FROM team

:

SELECT 'foo' FROM team

'foo' team ( IF 'foo' IN ... ). , .

, IF , . , :

IF (SELECT COUNT(*) FROM team WHERE teamName = new_parameter_name) THEN ...

, SQL (, ), .

+2

All Articles