I am actually running this app in my test env and I am running it against the local copy of the database. There are no other users and nobody else is connected to this database, I can surely chk for the blocked connection and thank u for your comments but i m not sure if that is the cause.

Here is the code for the procedure, can i put print statements in sqla procs like i used to do to debug my syabase procs: if yes is the syntax same as syabse:

ALTER PROCEDURE "DBA"."update_recall"( in @maskey decimal(10,2) ) 
/*
A patient ID is passed to this procedure which then updates the last recall dates in all recall types associated with this patient.
Written 10/29/2002 - BL - Enhancement for Monterey Peninsula Dental Group
*/
begin
  declare curs dynamic scroll cursor for select recall_number from recall_patient where master_key = @maskey;
  declare my_cursor dynamic scroll cursor for select isnull(fee_no,1) from audit where audit.master_key = @maskey and ada_no like '0%' and ada_no not like '0000%'
      group by fee_no order by fee_no asc;
  declare @fee_no decimal(2);
  declare @ls_fee varchar(2);
  declare my_sql varchar(300);
  declare @ld_date date;
  declare @recall_type real;
  declare @ld_last_rec date;
  declare @li_count integer;
  declare local temporary table tmp_recall(
    last_rec date null,
    recall_number real null,
    limit_to integer null default 0,
    ) on commit delete rows;open curs;
  type_loop: loop
    fetch next curs into @recall_type;
    if sqlstate <> '00000' then
      leave type_loop
    end if;
    open my_cursor;
    recall_loop: loop
      fetch next my_cursor into @fee_no;
      if sqlstate <> '00000' then
        leave recall_loop
      end if;
      select trim(cast(@fee_no as varchar)) into @ls_fee;
      execute immediate 'select max("today"), count(*) into @ld_date, @li_count from audit where audit.master_key = @maskey and fee_no = @fee_no and ada_no in (select ada_no from fee'
         || @fee_no || ' where recall_number = @recall_type)';
      if @ld_date is not null then
        select last_rec into @ld_last_rec from tmp_recall where recall_number = @recall_type;
        if @ld_last_rec is null then
          insert into tmp_recall( last_rec,recall_number,limit_to ) values( @ld_date,@recall_type,@li_count ) 
        else
          if @ld_date > @ld_last_rec then
            update tmp_recall set last_rec = @ld_date where recall_number = @recall_type
          end if;
          update tmp_Recall set limit_to = limit_to+@li_count where recall_number = @recall_type
        end if
      end if
    end loop recall_loop;
    close my_cursor
  end loop type_loop;
  close curs;
  update recall_patient
    ,tmp_recall
    set recall_patient.last_rec = tmp_recall.last_rec,
    recall_patient.limit_to = isnull(tmp_recall.limit_to,0)
    where recall_patient.recall_number = tmp_recall.recall_number
    and recall_patient.master_key = @MasKey;
  update recall_patient
    set limit_to = 0
    where not recall_number = any(select recall_number
      from tmp_recall)
    and recall_patient.master_key = @MasKey;
  update master
    set last_rec = (select max(recall_patient.last_rec)
      from recall_patient
      where recall_patient.master_key = @maskey)
    where master.master_key = @maskey;
  commit work
end