undersance
undersance

Reputation: 1

Delphi TFDStoredProc parameter default values missing (MS SQL)

I am migrating from ADO to FireDAC. After replacing TADOStoredProc to TFDStoredProc I have the following issue. My _OpenStp procedure opens a stored procedure having default values in its parameter list, and I don't want to pass all those parameters. E.g.

CREATE PROCEDURE [dbo].[usp_SearchDocument]
  @User_Id INT
  , @Window_Id INT = 10
  , @Page INT = 1
...

The core of my procedure:

procedure _OpenStp(
  const AConnection: TFDConnection;
  var AStp: TFDStoredProc;
  const AStpName: string;
  const AParamNameA: array of string;
  const AParamValueA: array of Variant);
var
  i: Integer;
begin
  if AStp <> nil then
  begin
    if AStp.Active then
      AStp.Close;
  end
  else
    AStp := TFDStoredProc.Create(nil);

  AStp.Connection := AConnection;
  AStp.StoredProcName := AStpName;
  AStp.Prepare;
  for i := Low(AParamNameA) to High(AParamNameA) do
    AStp.Params.ParamByName(AParamNameA[i]).Value := AParamValueA[i];

  AStp.Open;
end;

The Delphi code of the call:

_OpenStp(SomeConnection, SomeStp, 'usp_SearchDocument',
  ['User_Id'], [150]);

According to SQL Server Profiler the call was:

exec [dbo].[usp_SearchDocument] 
  @User_Id=150,
  @Window_Id=NULL,
  @Page=NULL

TFDStoredProc.Prepare doesn't seem to query the default values of the sp parameters. When I was using the ADO counterpart of my _OpenStp procedure, the TADOStoredProc.Parameters.Refresh method did that job:

procedure _OpenStp(
  const AConnection: TADOConnection;
  var AStp: TADOStoredProc;
  const AStpName: string;
  const AParamNameA: array of string;
  const AParamValueA: array of Variant);
begin
  if AStp <> nil then
  begin
    if AStp.Active then
      AStp.Close;
  end
  else
    AStp := TADOStoredProc.Create(nil);

  AStp.Connection := AConnection;
  AStp.ProcedureName := AStpName;
  AStp.Parameters.Refresh;
  for i := 0 to Length(AParamNameA) - 1 do
    AStp.Parameters.ParamByName(AParamNameA[i]).Value := AParamValueA[i];

  AStp.Open;
end;

SQL Server Profiler:

exec usp_SearchDocument 150,default,default

Unfortunately it isn't an option to rewrite the code to pass all of the parameters, I have to rely on sp parameter default values. Is there a way to modify the FireDAC version of my _OpenStp procedure to achieve this goal?

Edit: I don't even have information about the type of the parameters (see the _OpenStp procedure), I only know their names and the values to be set, so I can't create the TFDParams programmatically.

Edit#2: An EArgumentOutOfRangeException was thrown after deleting the unnecessary parameters:

for i := AStp.ParamCount - 1 downto 0 do
  if AStp.Params[i].Name <> '@RETURN_VALUE' then
  begin
    ExistsInArray := False;
    for j := Low(AParamNameA) to High(AParamNameA) do
      if Char.ToLower(AStp.Params[i].Name) = Char.ToLower(Format('@%s', [AParamNameA[j]])) then
      begin
        ExistsInArray := True;
        Break;
      end;

    if not ExistsInArray then
      AStp.Params.Delete(i);
  end;

Upvotes: 0

Views: 544

Answers (1)

Svetlana M
Svetlana M

Reputation: 1

We had the same problem. After deleting parameters add:

AStp.FetchOptions.Items := AStp.FetchOptions.Items - [fiMeta];
AStp.Unprepare;
AStp.Prepare;

Upvotes: 0

Related Questions