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>
- - , , .
!