Working With Result Sets Using Stored Procedures

This tutorial describes how to retrieve and modify result sets obtained from stored procedures using the TMSStoredProc component.

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQL Server" and "Connecting To SQL Server Compact"), how to create necessary objects on the server (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

Besides scalar variables, stored procedures can return result sets, i.e. the results of SELECT statements. Data can be inserted or modified in obtained result sets using the dataset functionality of the TMSStoredProc component.

The goal of this tutorial is to retrieve and modify data from the dept table using the TMSStoredProc component. The following stored procedure will be used to retrieve data:

CREATE PROCEDURE SelectDept
AS
BEGIN
  SELECT * FROM dept;
END

The Insert, Append, Edit, and Post methods of the TMSStoredProc component can be used to insert and modify data in obtained result sets. DML statements are generated by TMSStoredProc internally. The code below demonstrates using these methods:

[Delphi]

var
  sp: TMSStoredProc;
begin
  sp := TMSStoredProc.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    sp.Connection := con;

    // choose a stored procedure name
    sp.StoredProcName := 'SelectDept';

    // build a query for a chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // retrieve data
    sp.Open;

    // append record
    sp.Append;
    sp.FieldByName('deptno').AsInteger := 50;
    sp.FieldByName('dname').AsString := 'SALES';
    sp.FieldByName('loc').AsString := 'NEW YORK';
    sp.Post;

    // insert record
    sp.Insert;
    sp.FieldByName('deptno').AsInteger := 60;
    sp.FieldByName('dname').AsString := 'ACCOUNTING';
    sp.FieldByName('loc').AsString := 'LOS ANGELES';
    sp.Post;

    // to make the record with deptno=10 the current record
    sp.FindKey([10]);

    // modify record
    sp.Edit;
    sp.FieldByName('dname').AsString := 'RESEARCH';
    sp.FieldByName('loc').AsString := 'LOS ANGELES';
    sp.Post;
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMSStoredProc* sp = new TMSStoredProc(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    sp->Connection = con;

    // choose a stored procedure name
    sp->StoredProcName = "SelectDept";

    // build a query for a chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp->PrepareSQL();

    // retrieve data
    sp->Open();

    // append record
    sp->Append();
    sp->FieldByName("deptno")->AsInteger = 50;
    sp->FieldByName("dname")->AsString = "SALES";
    sp->FieldByName("loc")->AsString = "NEW YORK";
    sp->Post();

    // insert record
    sp->Insert();
    sp->FieldByName("deptno")->AsInteger = 60;
    sp->FieldByName("dname")->AsString = "ACCOUNTING";
    sp->FieldByName("loc")->AsString = "LOS ANGELES";
    sp->Post();

    // to make the record with deptno=10 the current record
    sp->FindKey(ARRAYOFCONST((10)));

    // modify record
    sp->Edit();
    sp->FieldByName("dname")->AsString = "RESEARCH";
    sp->FieldByName("loc")->AsString = "LOS ANGELES";
    sp->Post();
  }
  __finally
  {
    sp->Free();
  }
}