Hi guys,
Sinse yesterday we have a problem with server.
Today we found that all custom functions return 'null'.
Please help....
For example func.sql:
if (select proc_name
from sys.sysprocedure
where lower(proc_name)=lower('ExistProc')) is not NULL then
drop function ExistProc;
end if;

create function ExistProc(ProcName varchar(30)) returns integer
begin
declare pname varchar(30);
set pname=(select proc_name
from sys.sysprocedure
where lower(proc_name)=lower(ProcName));
if pname is null then
return 0
else
return 1;
end if;
end;
////////////////////
So, when we try too use ExistProc, we always get null as the return value.
for example:
if ExistProc(anyFunctionName)=1 then
drop function SimpleReverse;
end if;
or: select ExistProc(anyFunctionName) as res
As I said the problem is sinse yesterday, it had been working.
I think server is "Anywhere 8", i'm not sure.

I don't know what the answer is but have you tried running the SQL just to see what comes out ?

Hi Mike. Yes, the code:
select proc_name from sys.sysprocedure where lower(proc_name)=lower("NameOfAProc")
works.

Still guessing (I'm not a DBA) : I assume it's not a permissions thing and the user that's calling the function has permission to access these tables as does the user who compiled the functions.

it's always the same user.

Still guessing I'm afraid but at least it shows someone cares

• Has the database been restored recently?
• Have you tried just recompiling the function?
• Could there be two procs in the database with slightly different names ie MyProc and myproc - then your function will try and assign two values to a variable and go wrong?

Thanks Mike.
Answers are no,no and no... There are no changes we made. It have worked for years.