Creating Parameters at Runtime

From InterBase
Jump to: navigation, search

Go Up to Understanding Stored Procedure Parameters


If the name of the stored procedure is not specified in StoredProcName until runtime, no TParam objects will be automatically created for parameters and they must be created programmatically. This can be done using the TParam.Create method or the TParams.AddParam method.

For example, the InterBase stored procedure GET_EMP_PROJ, below, requires one input parameter (EMP_NO) and one output parameter (PROJ_ID).

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
  FOR SELECT PROJ_ID
  FROM EMPLOYEE_PROJECT
  WHERE EMP_NO = :EMP_NO
  INTO :PROJ_ID
  DO
    SUSPEND;
END

The Delphi code to associate this stored procedure with a TIBStoredProc named StoredProc1 and create TParam objects for the two parameters using the TParam.Create method is:

var
  P1, P2: TParam;
begin
  {...}
  with StoredProc1 do begin
    StoredProcName := 'GET_EMP_PROJ';
    Params.Clear;
    P1 := TParam.Create(Params, ptInput);
    P2 := TParam.Create(Params, ptOutput);
    try
      Params[0].Name := ‘EMP_NO’;
      Params[1].Name := ‘PROJ_ID’;
      ParamByname(‘EMP_NO’).AsSmallInt := 52;
      ExecProc;
      Edit1.Text := ParamByname(‘PROJ_ID’).AsString;
    finally
      P1.Free;
      P2.Free;
    end;
  end;
  {...}
end;

Advance To: