Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: Custom functions always return null, please help

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I don't know what the answer is but have you tried running the SQL just to see what comes out ?
    Code:
    select proc_name
    from sys.sysprocedure
    where lower(proc_name)=lower("NameOfAProc")

  3. #3
    Join Date
    Feb 2010
    Posts
    4
    Hi Mike. Yes, the code:
    select proc_name from sys.sysprocedure where lower(proc_name)=lower("NameOfAProc")
    works.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  5. #5
    Join Date
    Feb 2010
    Posts
    4
    it's always the same user.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?

  7. #7
    Join Date
    Feb 2010
    Posts
    4
    Thanks Mike.
    Answers are no,no and no... There are no changes we made. It have worked for years.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •