Hey Ayende,

Yeah, SQL 2008 needs to know the structure of the table "object", so you have to create that structure in SQL before you can utilise it. The other thing I haven't seen, albeit in my incredibly brief investigations, is a way to view what structures there are active against a db.

I have an extension method I talk about here:

http://grrargh.com/blog/playing-with-extension-methods-todatatable/

What that does is creates a DataTable from any type of IEnumerable, which you can then pass in to a call in .NET as a parameter for stored procs, etc, which accept a Table Valued Parameter "object".

Of course you have to place the DataTable columns in the same order as your TVP "object" is defined.

I have some very simple code and sql in C# that I used as a demo at a user group if you'd like me to shoot it through that would show you?

The other thing I forgot to mention in the last comment - these things look like they'll work beautifully with the MERGE in SQL 2008, because the Merge does an Insert or an Update depending on whether an object exists, you could pass in a complete table of modifications as one object/one call to your SQL stored proc.

And hopefully the Merge would then have less reads/scans through the index on updating the rows cleverly.

sorry.. I just got stuck on the part where it says you've "hit the 2,100 parameters limit of SQL Server too many times in the past".. What in the world were you doing? Maybe it's because I've never done whatever you were doing, but it sounds like you needed to rethink your approach.

..and I agree that table value feature would be really useful.

The hard-coded link is similar to the narrow minded solution they came up with for UDT's written in .NET code.

It's sad indeed, because all they really needed to do is adding something simple like Oracle has for years: array based parameters.

Well, I would prefer the only way to do something to be typed than untyped (if the both ways are not possible). You can use Func<T1, T2> approach (which is ugly, I know).

Ever considered passing XML into a stored proc? It's quite easy to shred it into a temp table and use and join however you like.

Now this is weird...

I searched for an example of how to shred XML and found

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

which strangely enough points right back to your blog.....

'I hit the 2,100 parameters limit of SQL Server too many times in the past, always when I had to do some large IN queries'

With Sql 2008 the answer is obvious, but on previous versions all you need is to pass a delimited string such as:

Select @params = '1,5,7,20,24,55'

and then split it and inner join it with your main data on the filtered column

Select * from MyData inner join dbo.Split(@params) Params on MyData.FilterColumn = Params.Data

Here is a splitting function taken from:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=1

CREATE FUNCTION dbo.Split

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1


While (Charindex(@SplitOn,@RowData)>0)

Begin

    Insert Into @RtnValue (data)

    Select 

        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))


    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

    Set @Cnt = @Cnt + 1

End


Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))


Return

END

Again, Its useful to be able to pass in full tables in Sql Server 2008, but I see this being mostly used to pass a List of Primary key values, rather than the table and its accompanying data, this would be more efficient. If this is a common case a couple of Table type definitions should suffice (one for passing int primary keys, varchar primary keys, etc)

Of course if you want to insert/update lots of data at once, I would avoid this technique and instead bulk insert all data to a temp table and then do a MERGE into the main table (since this is supported by sql 2008)

Just noticed in your reply to jeremy that you also tried fn_split...

Was it really underperforming in your scenario? What was the reasoning behind prefering the maximum number of parameters instead?

Ayende,

If you need to be generic (and I think you need to implement TVP in NHibernate ;-), you should query the database to obtain the schema of your TVP of your procedure.

But in the facts, if a procedure use a TVP, this means that the TVP type is already present in the database and the only thing you have to do in your mapping is to obtain it's structure.

In .Net side a DataTable is the object you need to pass as parameter to the procedure, then build the DataTable based on the structure needed by the TVP(s) of the concerned procedure.

To query the base to obtain the schema you should do a query like this one:

SELECT P.name,C.name,C.system_type_id,C.is_nullable,ST.name,ST.length

FROM sys.parameters P

INNER JOIN sys.objects O ON P.object_id = O.object_id

INNER JOIN sys.table_types T on P.user_type_id = T.user_type_id

INNER JOIN sys.columns C ON T.type_table_object_id = C.object_id

INNER JOIN sys.systypes ST ON C.system_type_id = ST.xusertype

WHERE 1=1

AND O.name = 'my_procedure_name'

This will give you a table with the table(s) value(s) parameter name, columns in TVP, their type and size.

Hope, it helps ;-)

Rui,

I don't want to have a SP that uses that, I want to be able to send arrays to the DB, that is all.