How do I return all values from a stored procedure?

Question

Forgive my naivety, but I am new to using Delphi with databases (which may seem odd to some).

I have setup a connection to my database (MSSQL) using a TADOConnection. I am using TADOStoredProc to access my stored procedure.

My stored procedure returns 2 columns, a column full of server names, and a 2nd column full of users on the server. It typically returns about 70 records...not a lot of data.

How do I enumerate this stored procedure programmatically? I am able to drop a DBGrid on my form and attach it to a TDataSource (which is then attached to my ADOStoredProc) and I can verify that the data is correctly being retrieved.

Ideally, I'd like to enumerate the returned data and move it into a TStringList.

Currently, I am using the following code to enumerate the ADOStoredProc, but it only returns '@RETURN_VALUE':

ADOStoredProc1.Open;
ADOStoredProc1.ExecProc;
ADOStoredProc1.Parameters.Refresh;

for i := 0 to AdoStoredProc1.Parameters.Count - 1 do
begin
  Memo1.Lines.Add(AdoStoredProc1.Parameters.Items[i].Name);
  Memo1.Lines.Add(AdoStoredProc1.Parameters.Items[i].Value);
end;

Answer 1

Take a look at this (just Googled it):

[http://www.scip.be/index.php?Page=ArticlesDelphi12&Lang=EN#Procedure][1]

Basically, a SQL Server stored procedure always returns one return value, but it can also create a result set, which you need to process like the data set returned from a regular select statement.

Answer 2

If your stored procedure returns a result set (rows of data), don't use ExecProc. It's designed to execute procedures with no result set. Use Open or Active instead, and then you can process them just as you are using Parameters:

ADOStoredProc.Open;

for i := 0 to ADOStoredProc1.Parameters.Count - 1 do
begin
  Memo1.Lines.Add(ADOStoredProc1.Parameters.Items[i].Name);
  Memo1.Lines.Add(ADOStoredProc1.Parameters.Items[i].Value);
end;

BTW, calling Open and then ExecProc causes problems; Open returns a result set, ExecProc then clears it because you're running the procedure a second time with no result set expected. I also don't think you need the Parameters.Refresh, but I'm not 100% sure of that.

Answer 3

Call Open to get a dataset returned

StoredProc.Open;
while not StoredProc.EOF do
begin
  Memo1.Lines.Add(StoredProc.FieldByName('xyz').Value);
  StoredProc.Next;
end;

Answer 4

Use Open to get the records from the StoredProc
Use either design-time Fields, ad-hoc Fields grabbed with FieldByName before the loop or Fields[nn] to get the values.

procedure GetADOResults(AStoredProc: TADOStoredProc; AStrings: TStrings);
var
  fldServer, fldUser: TField;
begin
  AStoredProc.Open;
  fldServer := AStoredProc.FieldByName('ServerName');
  fldUser := AStoredProc.FieldByName('User');
  while not AStoredProc.EOF do
  begin
    AStrings.Add(Format('Server: %s - / User: %s',[fldServer.AsString, fldUser.AsString]));
    // or with FFields and Index (asumming ServerName is the 1st and User the 2nd) and no local vars
    AStrings.Add(Format('Server: %s - / User: %s',[AStoredProc.Fields[0].AsString, AStoredProc.Fields[1].AsString]));
    AStoredProc.Next;
  end;
end;


//use like
  GetADOResults(ADOStoredProc1, Memo1.Lines);

Note: Fields[nn] allows to write less code but beware if the StoredProc changes the order of the returned columns.