procedure problem

[webmast3r]

hello i have a problem in a procedure...
am trying to recalculate all invoices in the database
invoices have header and details
here's the code that i am using

create  procedure "dba".RecalculateAllInvoices()
begin
  declare err_notfound exception for sqlstate value '02000';
  declare tn integer;
  declare Results dynamic scroll cursor for select transnum from supinvoice_header;

  open Results;
  ResultsLoop: loop
    fetch next Results into tn;
    if sqlstate=err_notfound then
      leave Resultsloop
    end if
    ;
    Call RecalculateInvoice(tn)
  end loop ResultsLoop;
  close Results;
  commit transaction
end

alter procedure "dba".RecalculateInvoice(@transnum integer)
as
begin
  declare @tax1ex double,
  @tax1 double,
  @amount double,
  @vatrate double,
  @invid integer,
  @rd double
  select @invid=id,@rd=realdiscount from supinvoice_header where transnum=@transnum
  select @vatrate=doublevalue from settings where id='VATRATE'
  select @tax1ex=sum(qty*costpsu*(100-discount)*(100-@rd)/10000),@tax1=sum(qty*costpsu*(100-discount)*istax1*@vatrate*(100-@rd)/10000),@amount=sum(qty*costpsu*(100-discount)*(1+(istax1*@vatrate))*(100-@rd)/10000)
    from supinvoice_detail where supinvheaderid=@invid
  update supinvoice_header set amount=@amount,tax1ex=@tax1ex,tax1=@tax1 where id=@invid
  commit transaction
end

am getting a cursor not open error...
what's the problem?

 

[dbalearner]

There is no such command as "alter procedure" in Sybase and also any user called "dba" (I will be surprised if there is one. I think you mean dbo!). You have to do this:

if exists(select 1 from sysobjects where name = 'RecalculateInvoice' and user_name(uid) = 'dbo')
begin
print 'dropping procedure RecalculateInvoice'
drop procedure RecalculateInvoice
END
go
create procedure dbo.RecalculateInvoice (@transum integer)
as
...

 

[webmast3r]

dbalearner,

for your information, there is the alter procedure command in sybase.. and actually once u create ur procedure, and u want to edit it, the sybase sql anywhere automatically converts the code into "alter procedure".. (this is where i pasted my code from".

so the alert procedure is not the issue.

another info: i have a username called dba which is the dbo.

so the issue, is not the user name, nor the "alter procedure"

i just have a problem in the cursor.
i guess the problem is because i am calling another procedure from within this procedure with a wrong cursor

the user is not the issue.. cos my dbo is named dba, neither is the "alter command".

to sum up what i really want is to create a procedure that:
1- will loop through all the invoices (supinvoice_header table) and retreive the transaction number from each invoice.
2- then run the procedure RecalculateInvoice (transactionnumber) - for every transaction number

Thanks again, and Cheers,
Dan

 

[dbalearner]

Dan,

Thanks for the info. I was actually referring to ASE not Sybase SQL anywhere. The term Sybase is very generic.