Grundsätzlich sind in den Artikel von Erland Sommarskog die Vorgehensweisen beschrieben:

http://www.sommarskog.se/arrays-in-sql-2008.html#Workarounds

http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-length

Aber es wird dort nur beispielhaft auf eine Liste von ganzen Zahlen eingegangen. Hier im Beispiel aber von ganzen Datensätzen.

Im nachfolgenden Beispiel wird nur ein TVP verwandt. Ziel im Beispiel ist es, Personendatensätze in eine Tabelle zu schreiben. Damit es nicht allzu langweilig ist, sollen vorhandene Sätze geändert und nicht vorhandene Sätze eingefügt werden.Das wird mittels MERGE und dem übergebenen TVP in einer Stored Procedure realisiert, für die eine weitere Stored Procedure als Wrapper verwendet wird, in der wiederum eine Funktion aufgerufen wird, die den BLOB als Parameter erhält und daraus den TVP zurück gibt, der dann an die aufzurufende Stored Procedure weitergegeben wird.

Achtung, man benötigt man die Numbers Funktion von Steve Kass:

http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly/

bzw. eine Numbers Tabelle (siehe z.B. http://www.glorf.it/blog/2010/08/17/sql-talk/number-helper-table )

Nun zu erst einmal die Tabelle und den passenden TVP erzeugen:

if object_id('dbo.Persons') is not NULL drop table dbo.Persons

if object_id('dbo.tvpTest') is not NULL drop procedure dbo.tvpTest

if object_id('dbo.fn_get_tvpPerson') is not NULL drop function dbo.fn_get_tvpPerson

if object_id('dbo.tvpTestWrapper') is not NULL drop procedure dbo.tvpTestWrapper

begin try drop type dbo.tvpPersons end try begin catch end catch

go

create table dbo.Persons

(

ID int primary key clustered,

Vorname nvarchar(30),

Name nvarchar(30),

GebDat datetime

)

go

create type dbo.tvpPersons as table

(

ID int primary key clustered,

Vorname nvarchar(30),

Name nvarchar(30),

GebDat datetime

)

go

Die Frage, ob ein TVP einen Primary Key benötigt und ggf. sogar unique constraints, muss man anhand des Verwendungszweckes klären. Hier im Beispiel sollen Datensätze bei Vorhandensein aktualisiert werden, sonst eingefügt werden. Nachfolgend die entsprechende Stored Procedure:

create proc dbo.tvpTest

@tvp as dbo.tvpPersons readonly

as

set nocount on

set xact_abort on

merge into dbo.Persons t

using @tvp s

on s.ID = t.ID

when matched then

update set

t.ID = s.ID,

t.Vorname = s.Vorname,

t.Name = s.Name,

t.GebDat = s.GebDat

when not matched then

insert

(

ID,

Vorname,

Name,

GebDat

)

values

(

s.ID,

s.Vorname,

s.Name,

s.GebDat

);

go

da die Client API nicht mit TVPs umgehen kann, nun die Wrapper Prozedur, die knapp gehalten ist, da die Funktionalität in der aufzurufenden Funktion liegt (der Parameter @p ist hier als output definiert um sich im SQL Server Profiler den Inhalt des Parameters als Textdata anschauen zu können)

create proc dbo.tvpTestWrapper

@p varbinary(max) output

as

set nocount on

set xact_abort on

declare @tvp as dbo.tvpPersons

insert into @tvp

select * from dbo.fn_get_tvpPerson(@p)

exec dbo.tvpTest @tvp

go

Die Funktion hat die Aufgabe, den BLOB in die entsprechenden Häppchen zu schneiden und in die entsprechenden Datentypen zu konvertieren. Was der Client da binär anliefert, lässt sich nicht immer einfach von binary zu dem gewünschten Datentypen konvertieren. Hier im Beispiel muss die Byte Reihenfolge beim INT umgedreht werden und das Datum als String übertragen werden. Natürlich könnte man alles als String übertragen, würde damit aber auch entsprechend mehr Bandbreite benötigen. Da bekannt ist, wie viele Bytes ein Datensatz im BLOB hat, ist mit Hilfe der Numbers Funktion das Teilen in einzelne Sätze kein Problem.

create function dbo.fn_get_tvpPerson

(

@p varbinary(max)

)

returns @tvp table

(

ID int primary key clustered,

Vorname nvarchar(30),

Name nvarchar(30),

GebDat datetime

)

as

begin

if datalength(@p) % 140 = 0

begin

insert into @tvp

select

convert

(

int,

substring(@p, 4 + (n -1) * 140, 1) +

substring(@p, 3 + (n -1) * 140, 1) +

substring(@p, 2 + (n -1) * 140, 1) +

substring(@p, 1 + (n -1) * 140, 1)

),

convert(nvarchar(30), substring(@p, 5 + (n -1) * 140, 60)),

convert(nvarchar(30), substring(@p, 65 + (n -1) * 140, 60)),

convert(nvarchar(16), substring(@p, 125 + (n -1) * 140, 16))

from dbo.Numbers(1, datalength(@p)/140)

end

return

end

Als Test-Client fungiert hier VBA mit ADO 2.8.

Nachfolgend die Klasse TVP4ADO28

Private adoStream As ADODB.stream

Private Type typRecord
ID As Long              '4 Byte
Vorname As String * 30  '60 Byte
Name As String * 30     '60 Byte
GebDat As String * 8    '16 Byte
End Type

Private Type typBinRecord
record(1 To 140) As Byte
End Type

Private Sub Class_Initialize()

Set adoStream = New ADODB.stream
adoStream.Type = adTypeBinary
adoStream.Mode = adModeReadWrite
adoStream.Open

End Sub


Public Sub AddRecord(ID As Long, Vorname As String, Name As String, GebDat As Date)

Dim t As typRecord
Dim r As typBinRecord
Dim s() As String

s() = Split(CStr(GebDat), ".")

t.ID = ID
t.Vorname = Vorname
t.Name = Name
t.GebDat = s(2) & s(1) & s(0)

LSet r = t

adoStream.Write r.record

End Sub

Public Property Get GetStream() As ADODB.stream

adoStream.Position = 0
Set GetStream = adoStream

End Property

Private Sub Class_Terminate()

adoStream.Close
Set adoStream = Nothing

End Sub

und hier der Aufruf (Der Application Name im Connection String dient zum einfachen Filtern im SQL Server Profiler)

Public Sub Test()

Dim conn As New ADODB.Connection
Dim com As New ADODB.Command
Dim i As Long

Set tvp = New TVP4ADO28

For i = 1 To 10000

tvp.AddRecord i, "Klaus", "Müller", "09.06.1957"

Next i


With conn

.ConnectionString = _
"Provider=SQLNCLI10;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=Test;" & _
"Data Source=MeinServer\MeineInstanz;" & _
"Application Name=MeineApplikation"

.CursorLocation = adUseClient
.IsolationLevel = adXactReadCommitted
.Mode = adModeReadWrite

.Open

End With

With com

.CommandType = adCmdStoredProc
.CommandText = "dbo.tvpTestWrapper"
.ActiveConnection = conn
.NamedParameters = False

.Parameters.Append .CreateParameter("@p", adVarBinary, adParamInputOutput, _
tvp.GetStream.Size, tvp.GetStream.Read)

Dim t As Single

t = Timer

.Execute Options:=adExecuteNoRecords

t = Timer - t

End With

Debug.Print t

conn.Close

Set com = Nothing
Set conn = Nothing
Set tvp = Nothing

End Sub

Viele Grüße

Christoph Ingenhaag