Results 1 to 12 of 12

Thread: Procedure error

  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Procedure error

    Why am I getting this error?


    Error:

    Procedure proc_searches expects parameter @end_date, which was not
    supplied.
    (return status = -6)



    Procedure:


    declare
    v_start_date date;
    v_woohoo_total number;
    v_recs number;


    begin

    select to_date('16-May-04') into v_start_date from dual;

    SELECT COUNT(caller_rec_no) into v_woohoo_total
    FROM rp.tinfo_req
    WHERE info_req_start_time = v_start_date
    and caller_rec_no = 9999;

    select count(*) into v_recs
    from rp.tsearches
    where event_date = v_start_date;

    if v_recs = 0
    then

    insert into rp.tsearches (event_date, trypo, per_trypo,
    woohoo, per_woohoo, swbc, per_swbc, total)
    values (v_start_date, 0, 0,
    v_woohoo_total, 100, 0, 0, v_woohoo_total);
    else
    update rp.tsearches
    set woohoo = v_woohoo_total
    where event_date = v_start_date;
    end if;


    update tsearches
    set total = trypo+woohoo+swbc
    where event_date = v_start_date;

    update tsearches
    set per_woohoo = trunc((woohoo/total)*100),
    per_trypo = trunc((trypo/total)*100),
    per_swbc = trunc((swbc/total)*100)
    where event_date = v_start_date;

    commit;

  2. #2
    Join Date
    Jun 2003
    Posts
    15
    This doesn't look like Transact SQL to me. The declare statement and statements ending in ";" are not syntactically correct for T-SQL.

    Anyway......the error in Sybase would mean that when the stored proc was created, it required an input paramenter @end-date. I would expect to see code such as:
    create proc myproc
    @end-date datetime
    as
    < sql code here>
    end

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    kendsr,

    Thank you for your response.
    I have no idea what this is. I am filling in for someone who is a Sybase DBA and I have no idea of how sybase works. I am executing this procedure thru sybase using isql and logging in as sa.

    What is strange though is that the same procedure for a different schema works fine when I run it thru isql but this one hangs.

    Here is the other procedure that I am speaking of that runs fine:

    declare
    v_start_date date;
    v_swbc_total number;
    v_recs number;


    begin
    select to_char((sysdate-1), 'DD-Mon-YY') into v_start_date from dual;

    SELECT COUNT(caller_rec_no) into v_swbc_total
    FROM rp.tinfo_req
    WHERE info_req_start_time = v_start_date
    and caller_rec_no = 8888;

    select count(*) into v_recs
    from rp.tsearches
    where event_date = v_start_date;

    if v_recs = 0
    then
    insert into rp.tsearches (event_date, typo, per_typo,
    woohoo, per_woohoo, swbc, per_swbc, total)
    values (v_start_date, 0, 0, 0, 0,
    v_swbc_total, 100, v_swbc_total);
    else
    update rp.tsearches
    set swbc = v_swbc_total
    where event_date = v_start_date;
    end if;


    update tsearches
    set total = typo+woohoo+swbc
    where event_date = v_start_date;

    update tsearches
    set per_woohoo = trunc((woohoo/total)*100),
    per_typo = trunc((typo/total)*100),
    per_swbc = trunc((swbc/total)*100)
    where event_date = v_start_date;

    commit;
    end;
    /

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    dual thing is in Oracle not in Sybase. Are you sure you are in right environment?

  5. #5
    Join Date
    Apr 2004
    Posts
    113
    Yes, I am sure.

    I go to a telnet session and I cd to the right directory and I type

    isql -Usa -P -SJESSE
    then I type
    use imrs
    go
    then I type
    exec proc_woohoo_searches "20040512"
    go

  6. #6
    Join Date
    Nov 2002
    Posts
    207
    run the following in isql and see if the output is same as what you have in your version.

    sp_helptext proc_woohoo_searches
    go

    if yes, pls. post the output.

  7. #7
    Join Date
    Apr 2004
    Posts
    113
    mkalsi,

    Thank u so much for your help. I will try it now and let you know.

  8. #8
    Join Date
    Jun 2003
    Posts
    15
    Is it possible your are runing sybase ASA versus sybase ASE?

    ASA =the "Anywhere" engine

    ASE = Sybase SQL Server now known as Aaptive Server Enterprise.

  9. #9
    Join Date
    Apr 2004
    Posts
    113
    kendrs,

    No it is ASE.

    I think I figured out my problem:

    For the procedure that runs fine I got:

    create proc proc_sc_searches
    @date_param char(8)
    as

    declare @start_date char(10)
    select @start_date = substring(@date_param, 5, 2)+"/"+substring(@date_param, 7, 2)+"/"+substring(@date_param, 1, 4)


    For the procedure that does not run fine I got:

    create proc proc_woohoo_searches
    @start_date char(10),
    @end_date char(10)
    as

    how do I convert the procedure that has the end date to be like the one that runs?

  10. #10
    Join Date
    Nov 2002
    Posts
    207
    Change it to...

    create proc proc_woohoo_searches
    @start_date char(10),
    @end_date char(10) = Null
    as

    and change @end_date processing in SP accordingly.

  11. #11
    Join Date
    Apr 2004
    Posts
    113
    mkalsi,

    What does:


    and change @end_date processing in SP accordingly mean?

    What is SP?

  12. #12
    Join Date
    Nov 2002
    Posts
    207
    SP is stored procedure. Since you are making @end_date as null, just wanted to make sure it does not run into error conditions in the code.

Posting Permissions

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