That can work quite well but be careful if you're using the .nodes() method in T-SQL to provide the table to join to. It doesn't have cardinality estimates (actually it "guesses" 10,000 rows) and can end up with some seriously badly performing query plans. Often we have to use it to extract the data into a temporary table and then join to it. (Wish it wasn't so).
Regards, Greg From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Jamie Surman Sent: Wednesday, 29 June 2011 4:13 PM To: ozDotNet Subject: Re: SQL CLR table as function parameter 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 <g...@mira.net> To: ozDotNet <ozdotnet@ozdotnet.com> 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
