FD Stored Proc calls a stored procedure on your database and makes the results available to the report. Use it when your data logic lives in a stored procedure rather than a plain SQL query. If you just need to run a SELECT statement, use FD Query instead.
FD Stored Proc requires an FD Database component to be present in the report.
Adding an FD Stored Proc to Your Report
- Make sure your report already has an FD Database component configured and connected
- Drag an FD Stored Proc component onto the designer surface
- In the Object Inspector, set the Database property to your FD Database component
- Set the StoredProcName property to the name of your stored procedure
- Click Params in the Object Inspector — Articles will fetch the parameter list from the database automatically
- Set the Expression for each input parameter that needs a value at runtime
- Set Active to True to test the stored procedure and load the result fields
Properties
Database
The FD Database component this stored proc uses to connect. You must set this before the stored proc can run.
StoredProcName
The name of the stored procedure on the database. Type the name exactly as it appears in your database. When you tab away from this field, Articles fetches the parameter list from the database automatically.
PackageName
The package name for databases that group stored procedures into packages, such as Oracle. Leave blank for SQL Server, MySQL, and Actian Zen.
CatalogName
The catalog (database) name. Only needed when the stored procedure lives in a specific catalog. Leave blank in most cases.
SchemaName
The schema name. Only needed for databases that use schemas such as SQL Server. Leave blank in most cases.
Params
The list of parameters for the stored procedure. Articles fetches these automatically when you set StoredProcName. For each input parameter, set the Expression to the value you want to pass at runtime. The expression can be a report variable, a dialog control value, or a fixed value.
Active
Set to True to execute the stored procedure and fetch results. Articles manages Active automatically when the report runs.
Input and Output Parameters
Stored procedures can have three types of parameters:
Input parameters — values you pass in to the stored procedure. Set the Expression for each input parameter in the Params property.
Output parameters — values the stored procedure passes back to the report. After the stored procedure runs, read output parameter values in script using ParamByName.
Input/Output parameters — parameters that work both ways. You pass a value in and the stored procedure may change it and pass a value back.
Example of reading an output parameter value in script after the stored proc runs:
procedure FDStoredProc1AfterOpen(DataSet: TDataSet);
var
TotalAmount: Double;
begin
TotalAmount := FDStoredProc1.Params.ParamByName('TotalAmount').AsFloat;
Report.Variables['TotalAmount'] := TotalAmount;
end;Stored Procedures That Return a Result Set
Some stored procedures return a set of rows, similar to a SELECT query. When your stored procedure returns rows, set Active to True in the designer to preview the fields, then bind your report bands to the FD Stored Proc component the same way you would bind them to an FD Query.
You can navigate and read the result set from script using the same methods available on FD Query:
FDStoredProc1.First;
while not FDStoredProc1.EOF do
begin
ShowMessage(FDStoredProc1.FieldByName('CustomerName').AsString);
FDStoredProc1.Next;
end;Stored Procedures That Do Not Return Rows
Some stored procedures perform an action — such as updating records or calculating totals — without returning a result set. For these, use ExecProc in script instead of setting Active to True:
procedure ReportOnStartReport(Sender: TObject);
begin
FDStoredProc1.Params.ParamByName('StartDate').AsDateTime := StrToDate('01/01/2026');
FDStoredProc1.Params.ParamByName('EndDate').AsDateTime := StrToDate('31/12/2026');
FDStoredProc1.ExecProc;
end;Stored Procedures That Return a Single Value
If your stored procedure is a function that returns a single value, use ExecFunc instead of ExecProc. ExecFunc returns the function result as a Variant:
procedure ReportOnStartReport(Sender: TObject);
var
Result: Variant;
begin
FDStoredProc1.Params.ParamByName('CustomerID').AsInteger := 1001;
Result := FDStoredProc1.ExecFunc;
Report.Variables['CustomerBalance'] := Result;
end;Finding Fields in Script
When working with a stored procedure result set in script, use FieldByName to read field values and FindField to safely check whether a field exists before reading it:
// FieldByName - raises an error if the field does not exist
ShowMessage(FDStoredProc1.FieldByName('CustomerName').AsString);
// FindField - returns nil if the field does not exist, safe to check first
var F: TField;
F := FDStoredProc1.FindField('CustomerName');
if F <> nil then
ShowMessage(F.AsString);Common Problems
The Params list is empty
Articles fetches parameters automatically when StoredProcName is set and the database is connected. If the list is empty, check that the Database property is set and Connected is True, then re-enter the StoredProcName.
“Stored procedure not found” error
Check that StoredProcName matches the stored procedure name in your database exactly. Some databases are case-sensitive. Also check that PackageName, CatalogName, and SchemaName are set correctly if your database requires them.
Output parameter values are empty after the stored proc runs
Read output parameters in the AfterOpen event, not BeforeOpen. Output values are only available after the stored procedure has finished executing.
The stored procedure runs but returns no rows
Check your input parameter values. If an input parameter has no value or the wrong value, the stored procedure may return an empty result set. Use ExecProc in the BeforeOpen event to verify parameters are set correctly before the procedure runs.
Active causes an error for a stored procedure that does not return rows
Do not set Active to True for stored procedures that perform actions rather than returning data. Use ExecProc instead. Setting Active on a non-result-set stored procedure will cause a FireDAC error.