调用存储过程【Delphi版】

A)返回结果为参数

var

Cn:TAdoConnection;

Sp:TAdoStoredProc;

recordCount:string;

begin

Cn := TAdoConnection.Create(nil);

try

Cn.ConnectionString := [数据库连接字符串];

Cn.LoginPrompt := False;

Cn.KeepConnection := True;

Cn.ConnectionTimeout:=2;

try

Cn.Open;

Sp := TAdoStoredProc.Create(nil);

try

Sp.Connection := Cn;

Sp.Close;

Sp.ProcedureName :='getStudentAmount';

Sp.Parameters.Refresh;//参数的初始化要放在refresh之后

Sp.Parameters.ParamByName('@recordCount').Value:=0;//存储过程中每一个参数都要

Sp.ExecProc; //初始化，包括“返回参数”

recordCount:= inttostr(Sp.Parameters.ParamByName('@recordCount').Value);

finally

Sp.Free;

end;

except

on E:EoleException do

begin

ShowMessage('数据库连接失败,请检查连接');

end;

end;

finally

Cn.Free;

end;

end;

B)返回结果为数据集

1)单个数据集

var
  Cn:TAdoConnection;
  Sp:TAdoStoredProc;
  nameFieldString:string;
begin
  Cn := TAdoConnection.Create(nil);
  Sp := TAdoStoredProc.Create(nil);
try
    Cn.ConnectionString := [数据库连接字符串];
    Cn.LoginPrompt := False;
    Cn.KeepConnection := True;
    Cn.ConnectionTimeout:=2;
try
      Cn.Open;
      Sp.Connection := Cn;
      Sp.Close;
      Sp.ProcedureName :='queryAllStudentInfo';
      Sp.Parameters.Refresh;
      Sp.Active:=True;//当返回结果是数据集时，一定要激活,但是“有参数时”必须放在参数初始化之后
      Sp.open;//返回的是参数时只能用ExecProc,返回的是数据集时用Open,返回的既有参数又有数据集合也用Open
      whilenot Sp.Eof do
begin
        nameFieldString:=Sp.FieldByName('SName').AsString;
        nameFieldString:=Trim(nameFieldString);
        Sp.Next;
end;
except
      on E:EoleException do
begin
        ShowMessage('数据库连接失败,请检查连接');
end;
end;
finally
    Sp.Free;
    Cn.Free;
end;
end

2)多个数据集

var
      Cn:TAdoConnection;
      Sp:TAdoStoredProc;
      AdoQuery:TAdoQuery;
      nameFieldString:string;
      aintf:_Recordset;
      RecordsAffected:OleVariant;
begin
    Cn := TAdoConnection.Create(nil);
try
       Cn.ConnectionString :=MainForm.DatabaseConnStr;
       Cn.LoginPrompt := False;
       Cn.KeepConnection := True;
       Cn.ConnectionTimeout:=2;
try
          Cn.Open;
          Sp := TAdoStoredProc.Create(nil);
          AdoQuery:=TAdoQuery.Create(nil);
try
            Sp.Connection := Cn;
            Sp.Close;
            Sp.ProcedureName :='CIB_GetCheckAccountFileInfo';
            Sp.Active:=True;//当返回结果是数据集时，一定要激活
            Sp.Parameters.Refresh;
            Sp.Active:=True;//当返回结果是数据集时，一定要激活,但是“有参数时”必须放在参数初始化之后

            Sp.open;//返回的是参数时只能用ExecProc,返回的是数据集时用Open,返回的既有参数又有数据集合也用Open
            aintf:=Sp.Recordset;
            AdoQuery.Recordset:=aintf;
whilenot AdoQuery.Eof do
begin
               nameFieldString:=AdoQuery.FieldByName('市场应用代码').AsString;
               nameFieldString:=Trim(nameFieldString);
               showmessage('市场应用代码为：'+nameFieldString);
               AdoQuery.Next;
end;
            aintf:=aintf.NextRecordset(RecordsAffected);
            AdoQuery.Recordset:=aintf;
whilenot AdoQuery.EOF do
begin
              nameFieldString:=AdoQuery.FieldByName('市场帐号').AsString;
              nameFieldString:=Trim(nameFieldString);
              showmessage('市场帐号为：'+nameFieldString);
              AdoQuery.Next;
end;
finally
            AdoQuery.Free;
            Sp.Free;
end;
except
          on   E:EoleException   do
begin
            ShowMessage('数据库连接失败,请检查连接');
end;
end;
finally
      Cn.Free;
end;
end;