Search code examples
delphistored-proceduresfiredacdefault-parameters

FireDAC - How to execute Stored procedure with default parameters?

i have created following stored procedure on MS SQL Server

CREATE PROCEDURE sppl_ParamTest
 @ID int = 666
AS
 BEGIN
  SELECT @ID;
 END

And trying to call it with FireDAC (without creating any parameters): 

FCommand:TFDCommand;
...
FCommand.Params.Clear;
FCommand.SQL.Text:='sppl_ParamTest';
FCommand.CommandKind:=skStoredProc
if FCommand.Params.Count=0 then
FCommand.Open;

But stored procedure returns NULL(supose to return 666)

As i understood, its caused by FireDAC magic power to query each procedure meta data, before procedure actually calls.

Is it possible to solve this ?

Solution

  • Setting this options to

    FetchOptions.Items:=[]

    or

    FetchOptions.Items := FetchOptions.Items - [fiMeta]

    Will prevent FireDac from querying meta data, and will allow to use param defaults.

    Thanks to whosrdaddy