Allow CLR UDT to return IEnumerable / Table / TVF from a method ( SqlMethod ) like XML.nodes() SQLCLR
Currently, it is only possible to return scalar values from User-Defined Types (UDTs), either as properties or methods.
Sometimes, however, one has a list / array / collection of items that needs to be returned. The only ways to accomplish this right now are either:
a) return XML / SqlXml and parse it with the T-SQL XML.nodes() function, or
b) return a serialized string to be parse by a T-SQL or SQLCLR TVF, or
c) pass the UDT itself into a SQLCLR TVF.
In all three cases, the mechanics of doing this are clunky, and extra steps are being taken to serialize / deserialize, resulting in increased CPU and elapsed times.
Here is a question on Stack Overflow where someone is asking how to accomplish this:
Creating a table method on a user defined type (like like 'nodes' on the XML data type) ( http://stackoverflow.com/q/30851523/577765 )
Upvotes: 4<=-=Mar 6 2017 3:54PM=-=>
Thanks for your feedback! We do not have any plans to change CLR user defined types at this time, but we will consider it for future releases.<=-=Mar 6 2017 3:55PM=-=>
Thanks for your feedback! We do not have any plans to change CLR user defined types at this time, but we will consider it for future releases.
Solomon Rutzky commented
I originally submitted this suggestion via: