Physical Modeling › Stored Procedures › DBMS Migration Example › Sybase Stored Procedure Template Code

Sybase Stored Procedure Template Code

The following is an example of a Sybase stored procedure template code:

CREATE PROCEDURE %TemplateName_%Substr(%Tablename,1,10)
Decl(c1,/*)%Decl(c2,*/)%Decl(v,@)
(@scannum integer)as

%:c1 This proc retrieves information on a customer based on the%:c2
%:c1 barcode number on their customer ID card, locates any%:c2
%:c1 outstanding movie rentals and calculates charges. Calcs%:c2
%:c1 overdue charges for the application running on the store%:c2
%:c1 cash register and inserts a record in the PYMT table%:c2
%:c1 The MOVIE RENTAL RECORD and PMNT tables are updated in the%:c2
%:c1 stored procedure to enhance security%:c2

%ForEachAtt(,"%:c1%:c2
"){DECLARE%:v%Substr(%AttFieldName,1,16)%AttDatatype}
DECLARE cursor1 CURSOR FOR
 SELECT %ForEachAtt(,","){AttFieldName}
  FROM %Tablename
  WHERE renting_customer=@scannum
 open cursor1
 fetch cursor1 into %ForEachAtt(,","){%:v%Substr(%AttFieldName,1,16)}

 while (@@sqlstatus = 0)
 begin
  IF (getdate()>@due_date)
  begin
  UPDATE %Tablename
  SET overdue_charge = datediff(day,getdate(),@due_date)*payment_amount
  WHERE CURRENT OF cursor1
 end
end
 INSERT INTO PMNT VALUES (0,@scannum,getdate(),0)
 commit

More information:

Stored Procedures

DBMS Migration Example