sql stored function gives an error

Question

I am trying to create a stored function to take one parameter called budget. The function should return the string 'LOW' for a budget less than or equal to 500000, 'MID' for a budget less than or equal to 850000, 'HIGH' for a budget less than or equal to 1200000, and 'ULTRA' for a budget above 1200000. But I am getting an error that doesn't make much sense to me.

Here is my function:

set term # ;

create procedure f_rating(budget int) 
as
begin
if (budget <= 500000) then
    return ('LOW');
else if (budget <= 850000) then
    return ('MID');
else if (budget <= 1200000) then
    return ('HIGH');
else 
    return ('ULTRA');
end #

I am still new to sql, so this syntax is based on examples online and such. Here is my error:

SQL Message : -804
An error was found in the application program input parameters for the SQL statement.

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -804
Function unknown
RETURN

Can anyone help me figure out what this means?

What database system are you using? – Tab Alleman Nov 07 '16 at 15:44 — Tab Alleman, Nov 07 '16 at 15:44
I am using Flamerobin Database admin – Austin Nov 07 '16 at 15:46 — Austin, Nov 07 '16 at 15:46

score 1 · Answer 1 · answered Nov 07 '16 at 15:46

1

procedure does not return any value, functions do.

Try:

create function f_rating(budget int) 
as

instead of

create procedure f_rating(budget int) 
as

answered Nov 07 '16 at 15:46

Bhavesh Ghodasara

1,981
2
15
29

score 1 · Accepted Answer · edited Feb 27 '21 at 07:59

The syntax for stored function is

CREATE FUNCTION funcname [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]
  <module-body>

So you have made two mistakes, you use procedure instead of function and you miss the RETURNS <type> part. Try

create function f_rating(budget int) RETURNS VARCHAR(5)
as
begin
if (budget <= 500000) then
    return 'LOW';
else if (budget <= 850000) then
    return 'MID';
else if (budget <= 1200000) then
    return 'HIGH';
else 
    return 'ULTRA';
end #

sql stored function gives an error

2 Answers2