Sql – How to return all values from a stored procedure

delphisqlstored-procedures

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;

Best Solution

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;