Returning TVP from stored procedures

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Lithium™
Posts: 42
Joined: Wed 23 Jun 2010 06:42

Returning TVP from stored procedures

Post by Lithium™ » Fri 15 Nov 2013 07:36

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!

AndreyZ

Re: Returning TVP from stored procedures

Post by AndreyZ » Fri 15 Nov 2013 14:01

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).

sofetch
Posts: 7
Joined: Fri 12 Oct 2007 08:33

Re: Returning TVP from stored procedures

Post by sofetch » Tue 26 Jul 2016 14:04

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.

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Returning TVP from stored procedures

Post by ViktorV » Wed 27 Jul 2016 11:37

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.

Post Reply