Well, I just wrote an article on this for SQL Server Mag but it might be
awhile before it appears. I'm also doing a session at VSLive Dallas that
discusses exactly how to do this, but again, you might not be able to get to
Dallas. So... in essence:
You have to create a Table Value type in the database. Basically:
CREATE TYPE Fred AS Table
(Stuff VarChar(50), MorStuff int)
The inbound stored procedure parameter MUST match this signature.
Next, you create a SP that accepts the Table-value type as an INPUT
parameter that's tagged as READONLY.
CREATE PROCEDURE George (@FredTableParameter dbo.Fred READONLY)
AS
Stuff (where the @FredTableParameter is referenced)
On the client, you create and populate a Structure that matches the TVP
signature. This can be a DataTable, any DataReader or any IEnumerable (like
a SqlDataRecord). You build a Parameter object as SqlDbType.Structured and
name the type and execute the Command.
hth
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
