|
CREATE PROCEDURE SPRCLIENT (
ASUPPLIER SMALLINT)
RETURNS (
ID INTEGER,
SNAME VARCHAR(25),
NAME VARCHAR(84),
CODE VARCHAR(25),
CLIENTTYPEID SMALLINT,
CLIENTTYPESNAME VARCHAR(25),
ADDRESSLOCATION VARCHAR(84),
ADDRESSLAW VARCHAR(84),
PHONE VARCHAR(25),
FAX VARCHAR(25),
TAXCODE VARCHAR(25),
TAXNUMBER VARCHAR(25),
STOREID SMALLINT,
STORESNAME VARCHAR(25),
BANKACCOUNTNO VARCHAR(25),
BANKMFO VARCHAR(25),
BANKOKPO VARCHAR(25),
BANKNAME VARCHAR(84),
PROFILEID SMALLINT,
PROFILESNAME VARCHAR(25),
OWNER VARCHAR(84),
CONTACTPERSON VARCHAR(84),
COMMENT VARCHAR(84),
SUPPLIER SMALLINT,
PARENTID INTEGER,
PARENTSNAME VARCHAR(25),
VOLUME CHAR(1),
PLACE CHAR(1),
TYPE CHAR(1),
DELIVERY CHAR(1),
ISINDIVIDUAL SMALLINT,
MAXDELAY SMALLINT,
PRICETYPEID SMALLINT,
PRICETYPESNAME VARCHAR(25))
AS declare variable Allowed smallint;
begin
for
select
C.ID,
C.SName,
C.Name,
C.Code,
C.ClientTypeID,
C.AddressLocation,
C.AddressLaw,
C.Phone,
C.Fax,
C.TaxCode,
C.TaxNumber,
C.StoreID,
C.BankAccountNo,
C.BankMFO,
C.BankOKPO,
C.BankName,
C.ProfileID,
C.Owner,
C.ContactPerson,
C.Comment,
C.Supplier,
C.ParentID,
PC.SName,
C.Volume,
C.Place,
C.Type,
C.Delivery,
C.IsIndividual,
C.MaxDelay,
C.PriceTypeID
from Client C left outer join Client PC on C.ParentID = PC.ID
where ((:aSupplier is Null)or(C.Supplier = :aSupplier))
into
:ID,
:SName,
:Name,
:Code,
:ClientTypeID,
:AddressLocation,
:AddressLaw,
:Phone,
:Fax,
:TaxCode,
:TaxNumber,
:StoreID,
:BankAccountNo,
:BankMFO,
:BankOKPO,
:BankName,
:ProfileID,
:Owner,
:ContactPerson,
:Comment,
:Supplier,
:ParentID,
:ParentSName,
:Volume,
:Place,
:Type,
:Delivery,
:IsIndividual,
:MaxDelay,
:PriceTypeID
do
begin
execute procedure eprClient_Allowed(:ID)
returning_values(:ClientTypeSName,:ProfileSName,:Allowed);
if (:Allowed=1) then begin
select SName
from Store
where ID = :StoreID
into :StoreSName;
PriceTypeSName = Null;
if (:PriceTypeID is not Null) then
select SName
from PriceType
where ID = :PriceTypeID
into :PriceTypeSName;
Suspend;
end
end
end
взято из рабочей базы, посмотри
|
