Stored procedures in Firebird/Interbase

Hi Ghanisen,

in InterBase you can create a stored procedure using the command CREATE PROCEDURE with the following syntax:

CREATE PROCEDURE ProcedureName InputParameterList
      RETURNS OutputParameterList
      AS
            DeclaringLocalVariabels
      BEGIN
            ProcedureBody
      END

ProcedureName conforms the InterBase naming convention and is unique within the database.


The easiest way to execute a procedure is: EXECUTE  PROCEDURE ProcedureName InputParameterList.

If you'd like to execute a procedure in an InterBase application (in other procedure or trigger) the syntax is:

EXECUTE  PROCEDURE  ProcedureName InputParameterList
     RETURNING_VALUES ParameterList

UPDATE example

In this example assume that a row was inserted or deleted. As input parameter gets the total of the invoice and the table Orders is modified with the new value of StockTotal.

SET TERM ^;
CREATE PROCEDURE SP_StockTotalModify    /*SP - Stored Procedure*/
      ( WhichOrder INTEGER, StockIncr DECIMAL(8,2) )
AS
BEGIN
      UPDATE Orders
            SET StockTotal = StockTotal + :StockIncr
            WHERE OrderID = :WhichOrder;
      EXIT;
END^
SET TERM ;^

In the query the variable WhichOrder is used in the WHERE condition and the variable StockIncr in the SET command. If the change increases the number is positiv, if decreases, negativ .
 
INSERT example

This example inserts a new row in the table InvoiceItems to an existing order. It calls the procedure SP_StockTotalModify above and transfers to the procedure the incrementation:

SET TERM ^;
CREATE PROCEDURE SP_NewInvoiceItemInsert
      ( WhichOrder INTEGER, WhichStockItem CHAR(10),
        UnitPrice DECIMAL(8,2), Quantity INTEGER )
AS
      DECLARE VARIABLE InterimTotal DECIMAL(8,2);
BEGIN
      /* stores the result of the interim calculation */
      InterimTotal = UnitPrice * Quantity;
      /* insert new row */
      INSERT INTO InvoiceItems
            VALUES( :WhichOrder, :WhichStockItem, :UnitPrice, :Quantity);
      /* calls an other procedure to modify the table containing the primary key */
      EXECUTE PROCEDURE SP_StockTotalModify
            :WhichOrder, :InterimTotal;
END^
SET TERM ;^

In this procedure the UnitPrice is multiplied by the Quantity, and the result will be stored in a variable. In the next step a new row is inserted in a normal way. At the end the value of the variable is transmitted to an embedded procedure to modify the sum value in the table Orders.

In this case you can see one advantage of the stored procedure: more operation is replaced by one called procedure.

I hope this will be sufficient but if you need more help write here.

wbr Janos

I think kacor gave you some details to manipulate in FB/IB databse. However, it is not enough for you to write an application using FB/IB database. I thinks you should visit here http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_download_documentation to download some document and read it over.
About SQL in FB/IB you can read http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_60_sqlref. You should remember that this document for IB version 6.0 or FB version 1.0x. Now IB version 7.5 and FB version 2.0 so they change very much. You should read change log document to get new features.
I hope you become an expert on IB/FB soon
Phuoc H. Nguyen