I need to pass the parameter as table value for a stored procedure in SQL Server. How to handle this in Delphi?
-
1Devart SDAC supports TVP, but ADO (Delphi TADOxxx components) does not support TVP.whosrdaddy– whosrdaddy2014-06-12 09:49:27 +00:00Commented Jun 12, 2014 at 9:49
Add a comment
|
2 Answers
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
1 Comment
user3733328
Hi Bummi, thanks for your quick answer. Is it possible to use clientdataset to pass table valued parameter to delphi.
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.
3 Comments
MartynA
Interesting answer. It might benefit from a (code) example in your answer of how to apply it in the context, say, of a Delphi project which is already using TAdoxxx components.
MartynA
Good, I'll vote for it. I think it only needs an outline, fwiw.
Marc Durdin
The translation of the OLEDB code to Delphi is more work than I had hoped. Given we have difficulty relying on the NSQLCLI version on client machines, we'll probably go with @bummi's answer above.