sql - Delphi - 将表值参数传递给 SQL Server 存储过程

Question

我需要将参数作为 SQL Server 中存储过程的表值传递。在Delphi中如何处理这个问题？

Answer 1

据我所知，没有简单的方法可以使用 Delphi 附带的组件来传递表参数。解决方法是使用临时表，该表可用于填充类型化表变量。

假设您的定义如下所示:

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

您可以这样调用该过程:

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