i don't confirm:
Code: Select all
uses
MSAccess;
procedure TForm1.Button1Click(Sender: TObject);
var
conn: TMSConnection;
q: TMSQuery;
sp: TMSStoredProc;
td: TMSTableData;
i: Integer;
begin
Memo1.Lines.Clear;
conn := TMSConnection.Create(nil);
conn.Server := '.';
conn.Database := 'testdb';
conn.Username := 'sa';
conn.Password := '123';
conn.Connect;
q := TMSQuery.Create(nil);
q.Connection := conn;
q.SQL.Add(' if not exists (select 1 from sys.types where name=''type_table_sdac'' and is_table_type=1) ');
q.SQL.Add(' create type type_table_sdac as table (id int) ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_table_type'') ');
q.SQL.Add(' drop procedure proc_test_table_type ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' create procedure proc_test_table_type @tab type_table_sdac readonly, @text nvarchar(max) output ');
q.SQL.Add(' as ');
q.SQL.Add(' begin ');
q.SQL.Add(' select @text=stuff((select isnull('', ''+convert(varchar(20), id), '''') from @tab for xml path('''')), 1, 2, '''') ');
q.SQL.Add(' end ');
q.ExecSQL;
sp := TMSStoredProc.Create(nil);
sp.Connection := conn;
td := TMSTableData.Create(nil);
td.Connection := conn;
td.TableTypeName := 'type_table_sdac';
td.Open;
for i := 0 to 9 do
begin
td.Insert;
td.FieldByName('id').AsInteger := Random(999) + 1;
td.Post;
end;
sp.StoredProcName := 'proc_test_table_type';
sp.ParamByName('tab').AsTable := td.Table;
sp.Execute;
for i := 0 to sp.Params.Count - 1 do
begin
Memo1.Lines.Add('Param index: ' + IntToStr(i));
Memo1.Lines.Add('Param name: ' + sp.Params[i].Name);
Memo1.Lines.Add('Param value: ' + sp.Params[i].Text);
Memo1.Lines.Add('====================================');
end;
sp.Free;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_table_type'') ');
q.SQL.Add(' drop procedure proc_test_table_type ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.types where name=''type_table_sdac'' and is_table_type=1) ');
q.SQL.Add(' drop type type_table_sdac ');
q.ExecSQL;
q.Free;
conn.Disconnect;
conn.Free;
end;
Tested on Lazarus 2.3 with FPC 3.2.2 x86, Windows 10 x64, SDAC 10.0.2 and MSSQL 2017