I love FillbyPrimaryKey with the overload that accepts a paramarray of keys. But I would like to us a SPROC to get the data on the back end as there are a number of joins I'd prefer to handle there.



What is the syntax for calling using FillbyStoreProcedure or cmd.CommandType = StoredProcedure and having, as a parameter, a list(of T)



( need to do this with both integer keys and system.guid keys )



TIA

Greg and I have already discussed this but I wanted to continue the discussion here for the benefit of other SF developers :



In SQL2008 the Table View Parameter has added an incredibly powerful tool for passing parameters into T-SQL sprocs. An old friend, Kevin Goff, has written some very good stuff for Code Magazine on all this and he sent me some links about the topic in general and TVPs in particular



http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-tsql-3.aspx

(go down to section 5....this converts a CSV to a table variable for subsequent join operations)



http://www.code-magazine.com/Article.aspx?quickid=0709031

(listings 12 and 13....this converts an XML string to a table variable)





http://www.code-magazine.com/Article.aspx?quickid=0807041

Finally, wrote about using the new table type in 2008 to pass a datatable as a parameter

(Tip #5)



This last link shows how to pass a datatable as a parameter into a sproc, where it can be used to do one big honkin' insert.



I am particularly interested in doing this with a table of keys in order to accomplish the same thing as fillbyprimarykey() using the paramarray overload.



NOTE: This is only relevant to SQL2008 and that is sql10 - spent an embarassing amount of time last night wondering why I was getting errors executing scripts, for getting the sql express db I was in ( sql9 - like vb 9 latest version, right? WRONG ) was sql2005.



Anyway, as soon as I get a complete walk-through on this done I'll post it . There is a huge amount of potential here and I'm amazed I haven't run across this solution before, though I'm sure somebody mentioned it but I just didn't get the implications at the time.

Well, this is some really cool stuff. The third article also introduced the hierarchyid data type in SQL which is just bloody awesome! I use hierarchies quite a lot and it was a pain in the...er...posterior. I also notices that it appears that T-SQL now can do OOP type stuff now. I.e. a variable that is a hierarchyid has methods! When did this happen?!?! Anyway, I'm very excited to delve into this more and hope to install SQL Server 2008 soon....Now back to planing for the migration of my companies SQL Server 2000 servers to SQL Server 2005....sigh...