I'm trying to wrap my head around functions in mySQL and I'm currently making one that checks the column account_description
and it's value to see if the description already exists.
If it does exist already, display a message saying so. However, if the description is not there, display a different message saying that it is not found.
Thanks!
MySQL Code:
DROP FUNCTION IF EXISTS test_glaccounts_description
DELIMITER //
CREATE FUNCTION test_glaccounts_description
(
check_description VARCHAR(50)
)
RETURNS VARCHAR(50)
BEGIN
DECLARE var_check VARCHAR(50);
SELECT
account_description INTO var_check
FROM
general_ledger_accounts
WHERE
account_description = check_description;
IF var_check = check_description THEN
SELECT 'That description already exists.';
ELSEIF var_check != check_description THEN
SELECT 'That description does not exist.';
END IF;
RETURN var_check;
END //
DELIMITER ;
SELECT
test_glaccounts_description(account_description) as 'Check'
FROM
general_ledger_accounts
WHERE
account_description = 'Accounting';
You can't use a SELECT to display the message in a stored function, you are restricted to returning a single value via the RETURN statement. You'll find this covered in the documentation
Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).