SS2k8: Table-valued Stored Procedure Parameters - How to?

  • Thread starter Thread starter Axel Dahmen
  • Start date Start date
A

Axel Dahmen

Hi,

I'm trying to call a stored procedure having a table-valued parameter through ADO.NET.

Can someone please enlighten me on how to do this?

TIA,
www.axeldahmen.de
Axel Dahmen
 
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)
____________________________________________________________________________________________
 
Hi William,

the client part of your explanation is exactly the information I was missing...

Gee, thanks a lot!!

Regards,
www.axeldahmen.de
Axel Dahmen




------------------------
 
Back
Top