CODEWITHSUNDEEP
delphistored-proceduresfiredacdefault-parameters
user3715238
user3715238

Reputation:

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 ?

Upvotes: 2

Views: 5691

Answers (2)

user3715238
user3715238

Reputation:

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

Upvotes: 2

MartynA
MartynA

Reputation: 30715

I added your definition of sppl_ParamTest to my Sql Server 2014 and executed the following code after minimal configuration of an FDConnection to point it at the server & database on it. The FDQuery is fresh from the component palette, with only its Connection property set.

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDQuery1.SQL.Text := 'sppl_ParamTest';
  FDQuery1.Open;
  Caption := FDQuery1.Fields[0].AsString;
end;

The form's caption displays 666 as expected.

Partial DFM (user name and password omitted):

  object FDConnection1: TFDConnection
    Params.Strings = (
      'Database=MATest'
      'Server=MAT410\ss2014'
      'DriverID=MSSQL')
    LoginPrompt = False
    Left = 32
    Top = 16
  end
  object FDQuery1: TFDQuery
    Connection = FDConnection1
    Left = 104
    Top = 16
  end

Update I think that the OP's problem is most likely arising because of a flaw in the implementation of TFDCommand and TFDStoredProc. Executing

sppl_ParamTest default

in Sql Server Management Studio correctly returns

666

So does opening an FDQuery with sppl_ParamTest default (or just sppl_ParamTest) as its command text.

However, with a TFDCommand's CommandText set to the same value, calling Execute on it produces this error

Could not find Stored Procedure 'MATest.dbo.sspl_ParamTest default'

Equally, doing 

FDStoredProc1.ParamByName('@ID').Value := 'default';

(unsurprisingly) provokes the error

Could not convert variant of type (String) into type (Integer)

on calling FDStoredProc.Open and has so far resisted my attempts to set a parameter type which will accept 'default' and correctly execute the sp.

Also, attempts to invoke the FDCommand with EmptyParam as the input parameter value fail to return the 666 value.

Upvotes: 4

Related Questions