CODEWITHSUNDEEP
sql-serversql-server-2008delphidelphi-7
tikskit
tikskit

Reputation: 339

TADOStoredProc returns either recordset or value of output param (not both)

My stored procedure takes one output param and returns recordset.

CREATE PROCEDURE MyProc
    @EntityCode BIGINT, @ResValue INT OUTPUT
AS
BEGIN
  SELECT .... WHERE Code=@EntityCode
  SET @ResValue = ...
END

I need to receive both the value of output param and recordset. So I do this:

function GetData(const EntityCode: Int64): Integer;
var
  Proc: TADOStoredProc;
  PEntityCode: ADODB.TParameter;
  PResValue: ADODB.TParameter;
begin
  Proc := TADOStoredProc.Create(nil);
  try
    Proc.Connection := ADOConnection1;
    Proc.CursorLocation := clUseServer;
    Proc.ProcedureName := 'MyProc';

    PEntityCode := Proc.Parameters.AddParameter;
    PEntityCode.Name := '@EntityCode';
    PEntityCode.DataType := ftLargeint;
    PEntityCode.Value := EntityCode;

    PResValue := Proc.Parameters.AddParameter;
    PResValue.Name := '@ResValue';
    PResValue.DataType := ftInteger;
    PResValue.Direction := pdOutput;

    //Proc.Prepared := True;
    Proc.Open;
    Result := PResValue.Value;
    while not Proc.Eof do
    begin
      Proc.Next;
    end;
  finally
    Proc.Free;
  end;
end;

Recordset is not empty but PResValue.Value is 0. If I call Proc.ExecProc then recordset is empty but PResValue.Value is assigned. Is it possible to receive both recordset and value of output param?

I've found out that OUTPUT param's value is assigned if recordset of MyProc consist of only one record. What does it mean?

Thanks!

Upvotes: 4

Views: 2725

Answers (1)

tikskit
tikskit

Reputation: 339

The solution is to close recordset before accessing the output variables:

Proc.Open;
while not Proc.Eof do
begin
  Proc.Next;
end;
Proc.Close;
Result := PResValue.Value;

Upvotes: 5

Related Questions