如何从存储过程返回所有值？

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

请原谅我的天真,但我不熟悉将Delphi与数据库一起使用(对某些人来说可能看起来很奇怪)。

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

我使用TADOConnection建立了与我的数据库(MSSQL)的连接。我正在使用TADOStoredProc来访问我的存储过程。

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.

我的存储过程返回2列,一列充满服务器名称,以及服务器上充满用户的第二列。它通常会返回大约70条记录......而不是大量数据。

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.

如何以编程方式枚举此存储过程?我能够在我的表单上删除一个DBGrid并将其附加到TDataSource(然后附加到我的ADOStoredProc),我可以验证数据是否正确检索。

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

理想情况下,我想枚举返回的数据并将其移动到TStringList中。

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

目前,我使用以下代码枚举ADOStoredProc,但它只返回'@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;

4 个解决方案

#1

Call Open to get a dataset returned

调用Open以获取返回的数据集

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

#2

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.

使用Open从StoredProc获取记录使用设计时字段,在循环之前使用FieldByName获取的临时字段或使用Fields [nn]获取值。

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.

注意:Fields [nn]允许编写更少的代码,但要注意StoredProc是否更改了返回列的顺序。

#3

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:

如果存储过程返回结果集(数据行),请不要使用ExecProc。它旨在执行没有结果集的过程。使用Open或Active,然后您可以像使用参数一样处理它们:

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.

BTW,调用Open然后执行ExecProc会导致问题; Open返回一个结果集,ExecProc然后清除它,因为你第二次运行该过程而没有预期的结果集。我也认为你不需要Parameters.Refresh,但我不是百分之百确定。

#4

Take a look at this (just Googled it):

看看这个(只是用Google搜索):

[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.

基本上,SQL Server存储过程总是返回一个返回值,但它也可以创建一个结果集,您需要处理该结果集,就像从常规select语句返回的数据集一样。

#1