Executing TIBStoredProc with one line of code

TIBStoredProc is handy, but multiple lines of code are required to execute it. The routine in this article handles preparing, assigning params, execution and transactions for you.

{ 
  ExecSP 
  Execute a InterBase Stored Procedure.  Transaction gets Committed after excution. 

  input: 
    SP = InterBase Stored Procedure 
    P  = Array with parameters for the SP. No param checking! 

  output: 
    Check the SP.Params for output (if any). 
} 
procedure TSPMod.ExecSP(SP: TIBStoredProc; P: array of Variant); 
var 
  A,B: Integer; 
begin 
  // make sure there's a transaction context 
  if not SP.Transaction.Active then 
    SP.Transaction.StartTransaction; 

  try 
    // make sure stored procedure is closed 
    SP.Close; 

    // prepare (attach params) 
    if not SP.Prepared then 
      SP.Prepare; 

    // Set all Input params 
    B := 0; 
    for A := 0 to SP.ParamCount-1 do 
      if (SP.Params[A].ParamType in [ptInput, ptInputOutput]) then 
      begin 
        SP.Params[A].Value := P[B]; 
        Inc(B); 
      end; 

    // run the procedure on the server 
    SP.ExecProc; 
  finally 
    // commit 
    SP.Transaction.Commit; 
  end; 
end; 

Examples: 

Assume you have a datamodule called SPMod. And assume it contains some stored procedures: 
  SPMod.spOpenenSession 
  SPMod.spGetTicketNr 

The following routines can be added to encapsulate the StoredProcs. 
   
// Example without returning data: 

procedure TSPMod.OpenSession(SessionID: Integer); 
begin 
  ExecSP(spOpenSession, [SessionID]); 
end; 


// Example with a integer as result 

function TSPMod.GetTicketNr: Integer; 
begin 
  ExecSP(spGetTicketNr, [CurrentSessionID]); 
  Result := spGetTicketNr.ParamByName('TicketNr').AsInteger; 
end;