sql stored function gives an error

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?

Answer

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 #

Advertisement

Answer