gadin gadin - 27 days ago 18
SQL Question

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?

ain ain
Answer

The syntax for stored function is

{CREATE [OR ALTER] | ALTER | RECREATE} FUNCTION <name>
[(param1 [, ...])]
RETURNS <type>
AS
BEGIN
    ...
END

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 #
Comments