Personally I tend to do this kind of stuff by passing the parameter as xml. In your SQL you can then join to the xml as though it were a table.
________________________________ From: Greg Keogh <[email protected]> To: ozDotNet <[email protected]> Sent: Wed, 29 June, 2011 5:17:40 Subject: RE: SQL CLR table as function parameter Oh oh! http://msdn.microsoft.com/en-us/library/ms131103.aspx has some bad news: Table-valued parameters are user-defined table types that are passed into a procedure or function and provide an efficient way to pass multiple rows of data to the server. Table-valued parameters provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with Transact-SQL. They also provide the potential for better performance. Table-valued parameters also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a table-valued parameter. 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. So I have to find some other convenient way of getting variable numbers of parameters into a managed function. Is there a T-SQL equivalent of String.Join(...) that easily joins many strings together with a separator, I could possibly accept that a proxy variable length parameter. Greg
