Creating and Using Stored Procedures
The InterBase 6 Data Definition Guide in the zip file recommended earlier was most useful. It gives these advantages of using stored procedures.
- Modular design: Applications that access the same database can share stored procedures, eliminating duplicate code and reducing the size of the applications.
- Streamlined maintenance: When a procedure is updated, the changes are automatically reflected in all applications that use it without the need to recompile and relink them; applications are compiled and optimized only once for each client.
- Improved performance: Stored procedures are executed by the server, not the client, which reduces network traffic, and improves performance, especially for remote client access.
We used the isql command line tool with a script file to enter the stored procedures FindID and CalcPercent. These are written in procedural SQL (psql). The second procedure calls the first. The syntax is different from Pascal and you are likely to make a few mistakes at first. For example, The assignment operator is = not :=, you declare variables after the keyword DECLARE not VAR and there must be no colon between the identifier and its type.
Note the SET TERM command to change the line terminator to # for the reason given below.
"CREATE PROCEDURE is a statement that must end with a terminator, just as all other SQL statements must. But the CREATE PROCEDURE statement contains other statements within it and these contained statements must also end with the terminator. If isql were to interpret semicolons as statement terminators, then procedures would execute during their creation rather than when they are called."
The code of Procedure FindID follows.
SET TERM # ; CREATE PROCEDURE FindID(Name VARCHAR(20)) RETURNS (ProgID INT, FirstName VARCHAR(15), LastName VARCHAR(15)) AS BEGIN SELECT ID, Forename, Surname FROM Programmer, Programs WHERE UPPER(Name) = UPPER(:Name) AND ID = ProgrammerID INTO :ProgID, :FirstName, :LastName; SUSPEND; END# SET TERM ;# GRANT EXECUTE ON PROCEDURE FindID to PUBLIC WITH GRANT OPTION;
The following command is one test of the procedure.
EXECUTE PROCEDURE FindID('Risk');PROGID FIRSTNAME LASTNAME ============ =============== =============== 2 Michael Wardley
The SUSPEND statement prompts the fetching of the data by the code that requests it. The colon is used to denote a variable rather than a column and is widely used in SQL although not always necessary in psql.
Procedure CalcPercent demonstrates the use of local variables and how to call another stored procedure.
SET TERM # ; CREATE PROCEDURE CalcPercent (Name VARCHAR(20)) RETURNS (Percent INT) AS DECLARE Count1 INT; DECLARE Count2 INT; DECLARE ProgID INT; DECLARE FirstName VARCHAR(15); DECLARE LastName VARCHAR(15); BEGIN EXECUTE PROCEDURE FindID :Name RETURNING_VALUES :ProgID, :FirstName, :LastName; SELECT COUNT(Name) FROM Programs WHERE ProgrammerID = :ProgID INTO Count1; SELECT COUNT(Name) FROM Programs INTO Count2; Percent = Count1 * 100 / Count2; SUSPEND; END# SET TERM ;# GRANT EXECUTE ON PROCEDURE CalcPercent to PUBLIC WITH GRANT OPTION;
The following command is one test of the procedure.
EXECUTE PROCEDURE CalcPercent('MarbleDrop');PERCENT ============ 4
The division with the operator / gives an integer result because we declared Percent as an integer. This procedure uses the COUNT function. Other built-in functions include AVG, MIN and MAX.
The coding of a procedure capable of returning several rows is trickier and needs a loop. We have not added this procedure to the script. We pasted it at the SQL> prompt within isql.
SET TERM # ; CREATE PROCEDURE FindSurname (Forename VARCHAR(15)) RETURNS (lastname VARCHAR(15)) AS BEGIN FOR SELECT Surname FROM Programmer WHERE Forename = :Forename INTO :lastname DO SUSPEND; END# SET TERM ; #
We tested it with an argument that returns more than a single record.
SELECT * FROM FindSurname('Adam');LASTNAME =============== Renak Greenberg
If you become keen on writing your own routines, please note that no more than 1,400 input parameters can be passed to a stored procedure!
Program FirebirdDemo4 shows you how to use stored procedures in Pascal. We supply the source for all demonstration programs and the database Contributions.fdb in db_contributions.zip. The Firebird demonstrations are set up with the database in the F:\Firebird directory, so put it there or change the DatabaseName property of the connection to its filepath on your system. The demonstrations need either Firebird server to be running or the embedded Firebird files (fbclient.dll etc.) to be in your program folder. The code follows a screenshot.

Program FirebirdDemo4 in action
uFirebirdDemo4.pas
unit uFirebirdDemo4; {$mode objfpc}{$H+} interface uses Classes, SysUtils, db, sqldb, IBConnection, FileUtil, Forms, Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls; type { TfrmSearch } TfrmSearch = class(TForm) dbedtPercent : TDBEdit; dsPercent : TDatasource; dsProgrammer : TDatasource; dbedtForename : TDBEdit; dbedtSurname : TDBEdit; edtSearch : TEdit; IBConnection1 : TIBConnection; Label1 : TLabel; sqlqProgrammer : TSQLQuery; sqlqPercent : TSQLQuery; SQLTransaction1 : TSQLTransaction; procedure edtSearchEditingDone(Sender : TObject); procedure FormClose(Sender: TObject; var CloseAction: TCloseAction); procedure FormCreate(Sender : TObject); end; var frmSearch: TfrmSearch; implementation {$R *.lfm} procedure TfrmSearch.edtSearchEditingDone(Sender: TObject); begin dbedtForename.Clear; dbedtSurname.Clear; dbedtPercent.Clear; sqlqProgrammer.Close; sqlqProgrammer.Params[0].AsString := edtSearch.Text; sqlqProgrammer.Open; sqlqPercent.Close; sqlqPercent.Params[0].AsString := edtSearch.Text; sqlqPercent.Open; end; procedure TfrmSearch.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin sqlqProgrammer.Close; sqlqPercent.Close; SQLTransaction1.Active:= False; IBConnection1.Connected:= False; end; procedure TfrmSearch.FormCreate(Sender: TObject); begin label1.Caption:= 'Type the name of a contributed program in the left edit box ' + 'to find the programmer and his/her percentage of contributions.'; end; end.
uFirebirdDemo4.lfm
object frmSearch: TfrmSearch Left = 307 Height = 88 Top = 186 Width = 434 Caption = 'Search' ClientHeight = 88 ClientWidth = 434 OnClose = FormClose OnCreate = FormCreate LCLVersion = '0.9.30' object edtSearch: TEdit Left = 0 Height = 28 Top = 55 Width = 152 OnEditingDone = edtSearchEditingDone TabOrder = 0 end object dbedtForename: TDBEdit Left = 160 Height = 28 Top = 55 Width = 120 DataField = 'FIRSTNAME' DataSource = dsProgrammer CharCase = ecNormal MaxLength = 15 TabOrder = 1 end object dbedtSurname: TDBEdit Left = 288 Height = 28 Top = 55 Width = 112 DataField = 'LASTNAME' DataSource = dsProgrammer CharCase = ecNormal MaxLength = 15 TabOrder = 2 end object dbedtPercent: TDBEdit Left = 408 Height = 28 Top = 55 Width = 24 DataField = 'PERCENT' DataSource = dsPercent CharCase = ecNormal MaxLength = 0 TabOrder = 3 end object Label1: TLabel Left = 0 Height = 50 Top = 2 Width = 430 AutoSize = False Color = clDefault ParentColor = False Transparent = False WordWrap = True end object dsProgrammer: TDatasource DataSet = sqlqProgrammer left = 136 top = 65520 end object sqlqProgrammer: TSQLQuery IndexName = 'DEFAULT_ORDER' Database = IBConnection1 Transaction = SQLTransaction1 ReadOnly = False SQL.Strings = ( 'Select * from FindID(:ProgName);' ) InsertSQL.Strings = ( '' ) Params = < item DataType = ftUnknown Name = 'ProgName' ParamType = ptUnknown end> left = 344 top = 65520 end object SQLTransaction1: TSQLTransaction Active = True Action = caNone Database = IBConnection1 left = 248 top = 65520 end object IBConnection1: TIBConnection Connected = True LoginPrompt = False DatabaseName = 'F:\Firebird\CONTRIBUTIONS.FDB' KeepConnection = False Transaction = SQLTransaction1 left = 40 top = 65520 end object sqlqPercent: TSQLQuery IndexName = 'DEFAULT_ORDER' Active = True Database = IBConnection1 Transaction = SQLTransaction1 ReadOnly = False SQL.Strings = ( 'SELECT * FROM CalcPercent(:Prog)' ) Params = < item DataType = ftUnknown Name = 'Prog' ParamType = ptUnknown end> left = 440 top = 65520 end object dsPercent: TDatasource DataSet = sqlqPercent left = 96 top = 64 end end
FirebirdDemo4.lpr
program FirebirdDemo4; {$mode objfpc}{$H+} uses Interfaces, Forms, uFirebirdDemo4; {$R *.res} begin Application.Initialize; Application.CreateForm(TfrmSearch, frmSearch); Application.Run; end.