Reputation: 1
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
Reputation: 1
We had the same problem. After deleting parameters add:
AStp.FetchOptions.Items := AStp.FetchOptions.Items - [fiMeta];
AStp.Unprepare;
AStp.Prepare;
Upvotes: 0