Page 1 of 1
Returning TVP from stored procedures
Posted: Fri 15 Nov 2013 07:36
by Lithium™
Hello!
I need to get a couple of table valued parameters (tvp) from stored procedure (sp).
And I'm interesting, if there is a way to return tvp as result from sp.
I'm known that "Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines."
But we have TMSTableData which declares and holds a global variable until it closes (I think it goes about so). If we do in the same manner and send to a sp an ID of opened TMSTableData... We'll be able to get tvp as results, won't we?
What do you think about it? Or could you advise me how to get table data from one sp to several TDatasets?
Thank you!
Re: Returning TVP from stored procedures
Posted: Fri 15 Nov 2013 14:01
by AndreyZ
Hello,
You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. It means that you cannot return table-valued parameters from stored procedures. That is a restriction of Table-Valued Parameters. For more information about this, please refer to
http://technet.microsoft.com/en-us/libr ... 10489.aspx
A dataset in Delphi cannot contain multiple result sets. You can work with multiple result sets only sequentially. For this, you can use the OpenNext method. Here is a code example:
Code: Select all
MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('select * from tablename1');
MSQuery1.SQL.Add('select * from tablename2');
MSQuery1.Open; // here the first result set is obtained
while not MSQuery1.Eof do begin
// some work
MSQuery1.Next;
end;
MSQuery1.OpenNext; // here the second result set is obtained
while not MSQuery1.Eof do begin
// some work
MSQuery1.Next;
end;
You can use this approach for both TMSQuery and TMSStoredProc components.
Another solution is to use several dataset components (TMSQuery or TMSStoredProc).
Re: Returning TVP from stored procedures
Posted: Tue 26 Jul 2016 14:04
by sofetch
Is this possible to use this OpenNext with tables returned by a stored procedure.
That thing does not work when I'm returning two result sets from a stored proc.
Re: Returning TVP from stored procedures
Posted: Wed 27 Jul 2016 11:37
by ViktorV
As we wrote earlier, the components allow working with multiple results returned by a stored procedure using the OpenNext method. Call of the OpenNext method will return the following result set if only the Options.DisableMultipleResults property is set to False.
Please make sure the Options.DisableMultipleResults property is set to False, and if it is, please compose a small sample demonstrating the described behavior and send it to viktorv*devart*com, including scripts to create database objects - in order for us to be able to answer you in more detail.