Table-Valued Parameters to CLR Procedures in SQL Server 2008 - possible?

Question

This page from SQL Server 2008 BOL, talks about CLR Stored Procedures and has a section labelled, "Table-Valued Parameters", which talks about how they can be advantageous. That's great - I'd love to use TVPs in my CLR procs, but unfortunately this seems to be the only reference in the universe to such a possibility, and the section doesn't describe what the syntax would be (nor does the further information linked at the end of the paragraph)

Sure, I can easily find descriptions of how to use TVPs from T-SQL procs, or how to do CLR procs in general. But writing a CLR proc that takes a TVP? Nothing. This is all highly unusal since the passing of multi-row data to a stored proc is a popular problem.

This leads me to wonder if the presence of the section on that page is an error. Somebody please tell me it's not and point me to more info/ examples.

[EDIT]

I was about to post this to one of the MS forums too when I came across this, which seems to be the final nail in the coffin. Looks like it can't be done.

Answer 1

I can find a lot more references. However, these are all for passing table-valued parameters to TSQL procedures, so that's of little use.

However, I've come to the conclusion that it's impossible. First, there is the list of mappings between CLR and SQL types. For table types there is no mapping, so the following does not work, for example:

[SqlProcedure]
public static void StoredProcedure(DataTable tvp, out int sum)
{
    return 42;
}

and then

CREATE TYPE MyTableType AS TABLE 
(
    Id INT NOT NULL PRIMARY KEY,
    [Count] INT NOT NULL
)
GO
CREATE ASSEMBLY ClrTest FROM '<somePath>'
GO
CREATE PROCEDURE ClrTest
AS EXTERNAL NAME ClrTest.StoredProcedures.StoredProcedure
GO

Whatever type you try (DataTable, DbDataReader, IEnumerable), the CREATE PROCEDURE call keeps generating an error 6552: CREATE PROCEDURE for "ClrTest" failed because T-SQL and CLR types for parameter "@tvp" do not match.

Second, the documentation on the page you linked to says: A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the SQL Server process.

I can not seem to find anywhere how to create a user defined table type in C#, but this also seems to be a dead end.

Maybe you can ask your question somewhere on a Microsoft forum. It's still odd that they mention table-valued parameters on the CLR sproc page but never explain how to implement this. If you find any solution, I'd like to know.

Answer 2

You can use a temporary table created and populated before you call the procedure and read the table inside the clr procedure.

Answer 3

The solution is to serialize your tabular data into a Json-formatted string then pass the string into your CLR proc. Within your clr proc or function you would parse the json to an IEnumerable, list, or tabular object. You may then work with the data as you would any other type of tabular data.

I have written some utilities capable of serializing any sql table into a Json formatted string. I would be happy to share them with anyone providing their e-mail address. Phil Factor has written a nice T-SQL Json parser he called parseJson. I have adapted his solution to the clr which performs much faster. Both accept a Json formatted string and produce a table from the string. I also have a variety of Json utilities I employ with both T-SQL and the CLR capable of serializing, parsing, inserting, deleting, and updating Json formatted strings stored in sql columns.