I'm trying to change several of our queries to use tabledata, but its seems that I can only make it work when executing stored procedures and not when using simple queries, ie.
Code: Select all
-- SQL server
create type MyTableData as table([id] int not null)
create procedure sp_someproc(@somedata MyTableData readonly)
as
select * from sometable t1 inner join @somedata t2 on (t1.id=t2.id)
-- Delphi
Data := TMSTableData.Create(nil);
Data.TableTypeName := 'MyTableData';
Data.Open;
Data.Append;
Data.Fields[0].AsInteger := 13;
Data.Post;
// This works
Proc := TMSStoredPRoc.Create(nil);
Proc.StoredProcName := 'sp_someproc';
Proc.ParamByName('somedata').AsTable := Data.Table;
Proc.Open;
// This does not work
Query := TMSQuery.Create(nil);
Query.SQL.Add('select * from sometable t1 inner join :somedata t2 on (t1.id=t2.id)')
Query.FieldByName('somedata').AsTable := Data.Table;
Query.Open;
Code: Select all
declare @somedata MyTableData
insert into @somedata values(13)
select * from sometable t1 inner join @somedata t2 on (t1.id=t2.id)
Am I doing it wrong or is table data only supported when calling stored procedures and functions?