Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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 )

4 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    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.

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base