Stored procedure returning select and output parameter
Posted: Tue 30 Aug 2022 13:05
Hello, in the last few years I started to use many stored procedures and it always works well when it returns results using output parameters or internally using SELECT without INTO.
However, there are cases where I feel the need to return both at the same time, that is, return a list of results (SELECT without INTO) and return output parameters at the same time (this is perfectly possible, even dbForge works perfectly , displaying the output parameters and the resulting select at the same time). However, in this case I can't capture the output parameter via MyDAC, I get the message that it doesn't exist. Maybe I'm not doing it the right way, so I bring my code just below:
Stored procedure:
Delphi code:
The error message: Exception class EDatabaseError with message 'Field '@out_total_rows' not found'.
See that in dbForge it works as expected:
However, there are cases where I feel the need to return both at the same time, that is, return a list of results (SELECT without INTO) and return output parameters at the same time (this is perfectly possible, even dbForge works perfectly , displaying the output parameters and the resulting select at the same time). However, in this case I can't capture the output parameter via MyDAC, I get the message that it doesn't exist. Maybe I'm not doing it the right way, so I bring my code just below:
Stored procedure:
Delphi code:
Code: Select all
function CreateStoredProc(const AStoredProcedureName: string): TMyStoredProc;
begin
Result := TMyStoredProc.Create(nil);
try
Result.Options.FieldsAsString := False;
Result.Options.EnableBoolean := False;
Result.Options.BinaryAsString := False;
Result.Connection := Connection;
Result.StoredProcName := AStoredProcedureName;
Result.PrepareSQL;
except
FreeAndNil(Result);
raise;
end;
end;
procedure GetAccountsSummarized(const ASearchText: string; const ALimitOffset, ALimitRowCount: Cardinal; out ATotalRows: Cardinal; out ASelectResults: TArray<TipGetAccountsSummarizedResult>);
var
LSelectResults: TList<TipGetAccountsSummarizedResult>;
LSelectResultsItem: TipGetAccountsSummarizedResult;
LGetAccountsSummarizedStoredProc: TMyStoredProc;
begin
LGetAccountsSummarizedStoredProc := CreateStoredProc('get_accounts_summarized');
LSelectResults := TList<TipGetAccountsSummarizedResult>.Create;
try
LGetAccountsSummarizedStoredProc.ParamByName('in_search_text').AsString := ASearchText;
LGetAccountsSummarizedStoredProc.ParamByName('in_limit_offset').AsLongWord := ALimitOffset;
LGetAccountsSummarizedStoredProc.ParamByName('in_limit_row_count').AsLongWord := ALimitRowCount;
LGetAccountsSummarizedStoredProc.Execute;
try
ATotalRows := LGetAccountsSummarizedStoredProc.FieldByName('@out_total_rows').AsLongWord;
while not LGetAccountsSummarizedStoredProc.Eof do
begin
LSelectResultsItem.AccountId := LGetAccountsSummarizedStoredProc.FieldByName('account_id').AsLongWord;
LSelectResultsItem.AccountName := LGetAccountsSummarizedStoredProc.FieldByName('account_name').AsString;
LSelectResultsItem.AccountEmail := LGetAccountsSummarizedStoredProc.FieldByName('account_email').AsString;
LSelectResultsItem.AccountDocument := LGetAccountsSummarizedStoredProc.FieldByName('account_document').AsString;
LSelectResults.Add(LSelectResultsItem);
LGetAccountsSummarizedStoredProc.Next;
end;
finally
LGetAccountsSummarizedStoredProc.Close;
end;
ASelectResults := LSelectResults.ToArray;
finally
LSelectResults.Free;
end;
end;
See that in dbForge it works as expected: