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.