stored procedure hangs

[Former Member]

I added a new varchar based column to a table. Values for this new column for all new records are populated via my powerbuilder app. the powerbuilder app when saving the datawindow that has this new column calls a stored procedure that processes some data from the table I have modified but not necesaarrily the column I have added to the table. All this works fine, the problem I encounter when I take the values from one of the columns in this table that existed before my changes and copy that data to my new column. As soon as I do that and I try to bring data for this individual in my app the stored procedure that works fine suddenly goes into lala land and hangs. I cannot understand why, I have tried to recompile the procedure, made the new column so there are no null values in it, nothing works. Can someone help please?

[MCMartin]

check if the stored procedure is blocking on another connection

[Former Member]

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.

[Breck_Carter]

Are there any other symptoms? Does the CPU usage go up, indicating there is an infinite loop? Does the disk light come on and stay on, indicating that too many rows are being updated (possibly because there's something missing from a WHERE clause)?

[Breck_Carter]

Please show us the code for the stored procedure.

[Former Member]

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

[Former Member]

ok: i found out the print statements, works the same way as sybase but if I have a print statement in my stored procedure and i execute it from interactive sql (sql central) where does the output get printed?

[VolkerBarth]

AFAIK it should show up in the Server's console window - as long as that is visible. If you're using a console log file (dbengX -o MyFile.txt), the output might appear in the log file, too.

Personally, I would recommend the MESSAGE statement which works similar to PRINT but gives much more control over the output destination - e.g. you can specify

MESSAGE 'Hello World!' TYPE INFO TO CLIENT;

and (depending on your client) that might be shown there. I don't know whether Sybase Central does show such messages but it could do so.