Delphi - pass table valued parameter to SQL Server stored procedure

Question

I need to pass the parameter as table value for a stored procedure in SQL Server. How to handle this in Delphi?

Answer 1

As far as I know there in no simple way to pass Table parameters, using the components shipped with Delphi. A workaround would be using a temporary table which can be used to fill a typed table variable.

Assuming your definition would look like this:

CREATE TYPE MyTableType AS TABLE 
( ID int
, Text varchar(100) )
GO

CREATE PROCEDURE P_Table 
    @Tab MyTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    Select * from @Tab -- dummy operation just return the dataset
END
GO

You could call the procedure like this:

var
  i: Integer;
begin
  // we create a temporary table since a table variable can obly be used for a single call
  DummyDataset.Connection.Execute('Create Table #mytemp(ID int,Text varchar(100))');
  DummyDataset.CommandText := 'Select * from #mytemp';
  DummyDataset.Open;
  for i := 0 to 10 do
  begin
    DummyDataset.Append;
    DummyDataset.Fields[0].Value := i;
    DummyDataset.Fields[1].Value := Format('A Text %d', [i]);
    DummyDataset.Post;
  end;
  MyDataset.CommandText := 'Declare @mytemp as MyTableType '
                         + 'Insert into @mytemp select * from #mytemp '  // copy data to typed table variable
                         + 'EXEC P_Table @Tab = @mytemp';
  MyDataset.Open;
  DummyDataset.Connection.Execute('Drop Table #mytemp');
end

Answer 2

The sample downloadable from http://msftdpprodsamples.codeplex.com/wikipage?title=SS2008%21Readme_Table-Valued%20Parameters is written in C++ but could readily be translated to Delphi.

Once you have translated that code to Delphi, you can use something like the following to make the result set accessible via good ole ADO:

SourcesRecordset := CreateADOObject(CLASS_Recordset) as _Recordset;
RSCon := SourcesRecordset as ADORecordsetConstruction;
RSCon.Rowset := rowset;

LDataSet := TADODataSet.Create(nil);
try
  // Only doing the first result set
  LDataSet.Recordset := SourcesRecordset;
  while not LDataSet.Eof do
  begin
    //... something
    LDataSet.Next;
  end;
finally
  LDataSet.Free;
end;

Note that CreateADOObject is a private function in Data.Win.ADODB.pas but it's pretty trivial.