CODEWITHSUNDEEP
sql-serverdelphisql-server-2017firedacdelphi-10.4-sydney
Marc Guillot
Marc Guillot

Reputation: 6455

Use SQL Server table-valued parameters without creating a type on the SQL Server

I'm trying to pass several values on a single parameter (table-valued parameter) and it works fine following the sample

C:\Users\Public\Documents\Embarcadero\Studio\21.0\Samples\Object Pascal\Database\FireDAC\Samples\DBMS Specific\MSSQL\TVP

SQL:

create type TVPType as table(Code integer, Name varchar(100), RegDate datetime, Notes varchar(max))
go

create table TVPTab(Code integer, Name varchar(100), RegDate datetime, Notes varchar(max))
go

Delphi:

procedure TForm1.btnQryManualSetupClick(Sender: TObject);
var
  oDS: TFDMemTable;
  i: Integer;
begin
  Start;

  FDQuery2.SQL.Text := 'insert into TVPTab (Code, Name, RegDate, Notes) ' +
    'select Code, Name, RegDate, Notes from :t';
  oDS := TFDMemTable.Create(nil);
  oDS.FieldDefs.Add('Code', ftInteger);
  oDS.FieldDefs.Add('Name', ftString, 100);
  oDS.FieldDefs.Add('RegDate', ftTimeStamp);
  oDS.FieldDefs.Add('Notes', ftMemo);
  FDQuery2.Params[0].DataTypeName := 'TVPType';
  FDQuery2.Params[0].AsDataSet := oDS;

  (FDQuery2.Params[0].AsDataSet as TFDMemTable).EmptyDataSet;
  for i := 1 to C_Recs do
    with FDQuery2.Params[0].AsDataSet do begin
      Append;
      Fields[0].AsInteger := i;
      Fields[1].AsString := 'str' + IntToStr(i * 10);
      Fields[2].AsDateTime := Now() + i;
      Fields[3].AsString := StringOfChar('x', 1000);
      Post;
    end;

  FDConnection1.StartTransaction;
  FDQuery2.Execute;
  FDConnection1.Commit;

  Done('TVP Qry manual setup');
end;

The problem with this code is that it forces me to create a type (in this case the TVPType type) on the server, and I would like to use these table-valued parameters without having to create any object on the server.

Can it be done ?. I have tried defining the parameter as ftDataset but I get an error: The data type READONLY cannot be found.

procedure TForm1.btnQryManualSetupClick(Sender: TObject);
var
  oDS: TFDMemTable;
  i: Integer;
begin
  Start;

  FDQuery2.SQL.Text := 'insert into TVPTab (Code, Name, RegDate, Notes) ' +
    'select Code, Name, RegDate, Notes from :t';
  oDS := TFDMemTable.Create(nil);
  oDS.FieldDefs.Add('Code', ftInteger);
  oDS.FieldDefs.Add('Name', ftString, 100);
  oDS.FieldDefs.Add('RegDate', ftTimeStamp);
  oDS.FieldDefs.Add('Notes', ftMemo);
  //FDQuery2.Params[0].DataTypeName := 'TVPType';
  FDQuery2.Params[0].DataType := ftDataset;
  FDQuery2.Params[0].AsDataSet := oDS;

  (FDQuery2.Params[0].AsDataSet as TFDMemTable).EmptyDataSet;
  for i := 1 to C_Recs do
    with FDQuery2.Params[0].AsDataSet do begin
      Append;
      Fields[0].AsInteger := i;
      Fields[1].AsString := 'str' + IntToStr(i * 10);
      Fields[2].AsDateTime := Now() + i;
      Fields[3].AsString := StringOfChar('x', 1000);
      Post;
    end;

  FDConnection1.StartTransaction;
  FDQuery2.Execute;
  FDConnection1.Commit;

  Done('TVP Qry manual setup');
end;

Upvotes: 1

Views: 564

Answers (1)

Russell Fox
Russell Fox

Reputation: 5435

No. If your multiple values are just a single row you can look into passing them as a comma-separated string and then use the STRING_SPLIT function, or using temp or permanent tables to store multi-row values.

Upvotes: 1

Related Questions