Issue with "table valued function"

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Scoobydoo
Posts: 2
Joined: Thu 18 Feb 2016 16:53

Issue with "table valued function"

Post by Scoobydoo » Thu 18 Feb 2016 17:11

Hi,
I have an issue with table values functions.

Using the "TMStoredProc" component I chose unter the tab "Stored Proc Call Generator" a table valued funktion from the drop down list and click on "Generate". I get the error "Unknown field type "table" (OLE DB code = 0h)". I have the same issue with the unidac components.

The function is created with:
CREATE FUNCTION [dbo].[udf_FunctionName](@Param datetime)
RETURNS @ReturnValue TABLE
As
begin
....
insert @ReturnValue
select * from result_table

Return
end

Is that a bug or is sdac not compatible with a return table?

Regards

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Issue with "table valued function"

Post by azyk » Fri 19 Feb 2016 11:21

TMSStoredProc doesn't support result of a User-Defined function for TMSParam parameters of dataset in case if this result is of table data type.

You can use the TMSQuery component to execute a User-Defined function, that returns a table data type. For example:

Code: Select all

  MSQuery.SQL.Text := 'select * from [dbo].[udf_FunctionName](:Param)';
  MSQuery.ParamByName('Param').AsDateTime := Value;
  MSQuery.Open;

Scoobydoo
Posts: 2
Joined: Thu 18 Feb 2016 16:53

Re: Issue with "table valued function"

Post by Scoobydoo » Tue 23 Feb 2016 11:49

Ok, that works!

Thank you very much!

Post Reply