Passing in results of a query to a CLR SqlFunction

Question

I have a stored procedure that needs to pass a result set from another stored procedure into a UDF, that in turn calls a CLR SqlFunction. What I can't figure out how to do is how to pass the result set into my CLR SqlFunction. I don't see a table SqlType nor can I find any examples online, maybe I am searching incorrectly? Also if you look at the last parameters passed in, that is where I am having problems. Don't know how to pass in the result set. Thanks!

<SqlFunction(DataAccess:=DataAccessKind.Read, SystemDataAccess:=SystemDataAccessKind.Read)> _
Public Function GetMatchedConstituents(ByVal ID As SqlGuid, ByVal CustomID As SqlString,
                                       ByVal KeyName As SqlString, ByVal FirstName As SqlString,
                                       ByVal MiddleName As SqlString, ByVal AddressBlock_Home As SqlString,
                                       ByVal AddressBlock_Business As SqlString, ByVal PostCode_Home As SqlString,
                                       ByVal PostCode_Business As SqlString, ByVal Phone_Home As SqlString,
                                       ByVal Phone_Business As SqlString, ByVal ResultSet As [WhatType? There isnt a SqlTable like I assumed]) As IEnumerable

    'do duplicate comparing logic here

End Function

Please post whatever code you have so far, even if it isn't working. Method signatures are especially helpful. And which version of SQL Server? — Yuck, Aug 12 '11 at 13:28

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

See this question Pass table as parameter to SQLCLR TV-UDF which has links to other related information. In short, TVP is not currently supported in SQL CLR.

If the result set is small enough you could convert it to an XML type and pass that as a parameter to your SQL CLR function (SqlXml).

You could also have the stored procedures set up temporary tables to share. It's sloppy, but may end up being your only option.

edited May 23 '17 at 11:53

Community

1
1

answered Aug 12 '11 at 13:56

Yuck

49,664
13
105
135

Ahhh...well I think that the XML type is out of the questions seeing I will have millions of results that have to be passed in. Looks like we may have to rethink this. :( Thanks though! – Chris Whisenhunt Aug 12 '11 at 14:11

Passing in results of a query to a CLR SqlFunction

1 Answers1