How to get multiple result set in dbexpress tsqlstoredproc

Question

I want get multiple record or result sets from tsqlstoredproc in Delphi XE3 or I want get multiple record or result sets from tsqlquery.

For example:

ds1,ds2: tsqldataset;
 begin
 sqlstoredproc1.open; //or Active:=true
 ds1:=sqlstoredproc1;
 ds2:=sqlstoredproc1.nextrecordset;

thanks for all

Answer 1

The Button1Click handler below shows how to do this for a TSqlStoredProc. TSqlQuery does not implement a NextRecordSet function. This is unlike the TAdoXXX set of Stored Proc + DataSet components which all provide access to the NextRecordSet function of their underlying MDac RecordSet objects.

All you need do is to declare a TCustomSqlDataset variable and then assign the SqlStoredProc's NextRecordSet function result to it.

A thing to be aware of is that the CustomSqlDataSet returned by TSqlStoredProc.NextRecordSet is given the same owner as the TSqlStoredProc. This is fine if the SqlStoredProc is on a form, because it and the CustomSqlDataSet will be freed when the form is. But if you create an ownerless SqlStoredProc in code, you will need to free any CustomSqlDataSet returned by its NextRecordSet yourself or you will have a memory leak.

Btw, the variable you assign .NextRecordSet to needs to have a lifetime at least as long as you want to use the NextRecordSet for. In other words, it's no use assigning the result of NextRecordSet to a local variable of a procedure which exits immediately afterwards (I wasn't sure whether the code in your q was supposed to be a local procedure or not).

  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    SQLStoredProc1: TSQLStoredProc;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    ClientDataSet2: TClientDataSet;
    DataSetProvider2: TDataSetProvider;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    NextDataSet : TCustomSqlDataSet;
  end;

var Form1: TForms;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  NextDataSet := SqlStoredProc1.NextRecordset; 
  DataSetProvider2.DataSet := NextDataSet;
  ClientDataSet2.Open;
end;

Partial DFM (TSqlConnection edited for security reasons)

object Form1: TForm1
  Left = 265
  Top = 197
  Width = 527
  Height = 358
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 8
    Top = 8
    Width = 320
    Height = 120
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DBGrid2: TDBGrid
    Left = 8
    Top = 160
    Width = 320
    Height = 120
    DataSource = DataSource2
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Button1: TButton
    Left = 352
    Top = 24
    Width = 137
    Height = 25
    Caption = 'Get next recordset'
    TabOrder = 2
    OnClick = Button1Click
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 232
    Top = 32
  end
  object SQLStoredProc1: TSQLStoredProc
    Active = True
    MaxBlobSize = -1
    Params = <>
    SQLConnection = SQLConnection1
    StoredProcName = 'getfilesdetails2'
    Left = 72
    Top = 32
  end
  object DataSource2: TDataSource
    DataSet = ClientDataSet2
    Left = 176
    Top = 232
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = SQLStoredProc1
    Left = 112
    Top = 32
  end
  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 192
    Top = 32
  end
  object SQLConnection1: TSQLConnection
    ConnectionName = 'MSSQLConnection'
    DriverName = 'MSSQL'
    GetDriverFunc = 'getSQLDriverMSSQL'
    LibraryName = 'dbexpmss.dll'
    LoginPrompt = False
    Params.Strings = (
      'DriverName=MSSQL'
      'HostName=aaaa'
      'DataBase=bbbb'
      'User_Name=cccc'
      'Password='
      'BlobSize=-1'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'MSSQL TransIsolation=ReadCommited'
      'OS Authentication=False')
    VendorLib = 'oledb'
    Connected = True
    Left = 32
    Top = 32
  end
  object ClientDataSet2: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider2'
    Left = 136
    Top = 232
  end
  object DataSetProvider2: TDataSetProvider
    Left = 88
    Top = 232
  end
end