Executing Stored Procedure

FireDAC offers TADStoredProc component to execute database stored procedures, as a stored procedure may be executed directly.

Using TADStoredProc

In general, the TADStoredProc may be setup at design-time and / or at run-time. The TADStoredProc transparently generates a SQL command to call a stored procedure basing on the TADStoredProc property values. Before executing FireDAC sends the parameter values to a DBMS, then executes a stored procedure and receives the output parameter values. 

Note, FireDAC does not support parameters with the default values. 


Setting stored proc at design time

To execute a stored procedure drop a TADStoredProc component on a form. TADStoredProc.Connection will be automatically set to point to a TADConnection on this form, if any. 

Then optionally set CatalogName, SchemaName, PackageName properties or choose their values from a drop down list. After setting StoredProcName and when fiMeta is included into FetchOptions.Items, the Params collection will be filled in automatically. 

To unify parameter names, set ResourceOptions.UnifyParams to True. For example, this will exclude '@' prefix from the SQL Server stored procedure parameter names. 


Setting stored proc at run time

That is similar to design time, just must be done using code: 


ADStoredProc1.StoredProcName := 'my_proc';
// now the Params collection is filled in


The Prepare call fills the Params collection using the mkProcArgs meta data, when fiMeta is included into FetchOptions.Items. Note:

  • mkProcArgs querying may be time consuming;
  • application cannot change parameter definitions after the Params is filled. For example, assignment to TADParam.AsXxxx properties implicitly sets the parameter data type.

To avoid above issues, exclude fiMeta from FetchOptions.Items, to avoid automatic rebuilding of Params collection at Prepare call. And semi-automatically fill the Params collection before the Prepare or ExecProc calls using the code: 


ADStoredProc1.StoredProcName := 'my_proc';
ADStoredProc1.FetchOptions.Items := ADStoredProc1.FetchOptions.Items - [fiMeta];


Or manually: 


ADStoredProc1.StoredProcName := 'my_proc';
ADStoredProc1.FetchOptions.Items := ADStoredProc1.FetchOptions.Items - [fiMeta];
with ADStorecProc1.Params do begin
  with Add do begin
    Name := 'Par1';
    ParamType := ptInput;
    DataType := ftString;
    Size := 50;
  with Add do begin
    Name := 'Par2';
    ParamType := ptOutput;
    DataType := ftInteger;


Using packaged procedures

To use a packaged procedure, application must specify the package name. That must be done by either of ways:

  • set PackageName. The package name may be specified in [<catalog name>.][<schema name>.]<package name> format.
  • set StoredProcName. The stored procedure name may be specified in [<catalog name>.][<schema name>.]<package name>.<stored proc name> format.

To choose the overloaded procedure, application must specify the Overload property. For example: 


ADStoredProc1.PackageName := 'SYS.DBMS_SQL';
ADStoredProc1.Overload := 1;
ADStoredProc1.StoredProcName := 'BIND_VARIABLE';


Executing the stored procedure

To execute a stored procedure, which does not return a result set, use the ExecProc methods. To execute a stored function, use the ExecProc or ExecFunc methods, where ExecFunc returns the function value. If a stored procedure returns a result set, then exception "[FireDAC][Phys][Oracl]-310. Cannot execute command returning result sets" will be raised. 

Note, there are few overloaded ExecProc and ExecFunc methods, allowing to avoid TADStoredProc property usage and specify all required information as method arguments. For example: 


ADStoredProc1.StoredProcName := 'MY_PROC';
ADStoredProc1.Params[0].Value := 100;
ADStoredProc1.Params[1].Value := 'audi';


Or more compact: 


ADStoredProc1.ExecProc('MY_PROC', [100, 'audi']);


To execute a stored procedure, returning a result set and open this result set, use the Open methods. If a stored procedure returns no result sets, then exception "[FireDAC][Phys][Oracl]-308. Cannot open / define command, which does not return result sets" will be raised. If the stored procedure returns multiple result sets, then check "Command Batches" for details. 

Note, that stored procedure may be executed asynchronously


Using TADQuery

The main difference of TADStoredProc from a TADQuery, is that TADStoredProc automatically generates a stored procedure call using the parameters information. The SQL code calling a stored procedure may be executed directly using any FireDAC method of the SQL command execution. For example, call an Oracle packaged proc using TADQuery: 


with ADQuery1.SQL do begin
  Add('  sys.dbms_sql.bind_variable(:c, :name, :value');
ADQuery1.Params[0].AsInteger := 1;
ADQuery1.Params[1].AsString := 'p1';
ADQuery1.Params[2].AsInteger := 100;


Using TADCommand

Finally, you can use TADCommand to execute a stored procedure. Most of the above discussions may be applied to the TADCommand too.

What do you think about this topic? Send feedback!