Christoph Ingenhaag

Eine Möglichkeit, eine fortlaufende Summe zu erzeugen ist die Nachfolgende.

Voraussetzung ist eine fortlaufende Nummer ohne Lücken und aus Gründen der Performance ein Index auf die fortlaufenden Nummer.

Da man diese Voraussetzungen selten vorfindet, lege ich eine entsprechende temporäre Tabelle an, die diesen Voraussetzungen genügt.

Aus den Testdaten

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

Wenn man die Rückgabe eines Resultsets einer Stored Procedure ad hoc in einer neuen Tabelle oder direkt weiterverarbeiten möchte, kann man mit Hilfe von OPENROWSET (siehe Hilfe, Ad Hoc Distributed Queries müssen erlaubt sein) ein wenig tricksen:

Der Einfachheit halber habe ich für das Beispiel die Stored Procedure sp_help verwendet:

Rückgabe in eine Tabelle schreiben

select *

into #t1

from openrowset

(

'SQLNCLI',

'Server=MeinServer\MeineInstanz;Trusted_Connection=yes;',

'exec sp_help;'

) as a;

select * from #t1

go

drop table #t1

go

oder einfach als Funktion weiterverwenden

create function dbo.fn_help()

returns table

as

return

(

select *

from openrowset

(

'SQLNCLI',

'Server=MeinServer\MeineInstanz;Trusted_Connection=yes;',

'exec sp_help;'

) as a

);

go

select * from dbo.fn_help()

drop function dbo.fn_help

Dieses Vorgehen sollte aber die Ausnahme bleiben, ad hoc eben.

Ergänzend siehe auch

http://www.insidesql.org/blogs/uricken/2010/08/20/verwendung-von-udf-auf-linked-server

Viele Grüße

Christoph Ingenhaag

IN Operator

test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)

Bekannt ist, das man mit dem IN Operator prüfen kann, ob Werte in einer Spalte vorhanden sind:

where Spalte1 in (1,2,3)

das es auch andersherum geht, ist eher unbekannt. Obwohl nach Onlinehilfe die test_expression "nur" ein gültiger Ausdruck sein muss:

where 1 in (Spalte1, Spalte2, Spalte3)

siehe hierzu auch:

http://blogs.technet.com/wardpond/archive/2009/09/04/database-programming-did-you-know-in-can-do-this.aspx

http://sqlblog.com/blogs/denis_gobo/archive/2009/04/09/13186.aspx

Gültige Ausdrücke sind

{ constant | scalar_function | [ table_name. ] column | variable 

        | ( expression ) | ( scalar_subquery ) 

    | { unary_operator } expression 

    | expression { binary_operator } expression 

    | ranking_windowed_function | aggregate_windowed_function

}

also where (Ausdruck) in (Spalte1, Spalte2, Spalte3)

Aber der Ausdruck darf nicht mehr als eine Zeile und Spalte zurückgeben (skalarer Ausdruck). Mit einem kleinen Trick kann man aber trotzdem mehrere Werte übergeben. Dazu ein Beispiel mit Testdaten:

begin try drop table #t end try begin catch end catch;

select

identity(int, 1, 1) id,

*

into #t

from

(

values

(0,0,0,0,0,0), (1,0,0,0,0,0), (0,1,0,0,0,0),

(0,0,1,0,0,0), (0,0,0,1,0,0), (0,0,0,0,1,0),

(0,0,0,0,0,1), (2,0,0,0,0,0), (0,2,0,0,0,0),

(0,0,2,0,0,0), (0,0,0,2,0,0), (0,0,0,0,2,0),

(0,0,0,0,0,2), (3,0,0,0,0,0), (0,3,0,0,0,0),

(0,0,3,0,0,0), (0,0,0,3,0,0), (0,0,0,0,3,0),

(0,0,0,0,0,3)

) t(sp1, sp2, sp3, sp4, sp5, sp6);

Ein Crossjoin mit den zu überprüfenden Werten funktioniert:

select distinct id

from #t,

(

select arg

from (values (1), (2)) a(arg)

) t

where

arg in (sp1, sp2, sp3, sp4, sp5, sp6);

Noch ein anderes Beispiel, mit dem man alle Lottozahlen 6 aus 49 inkl. Zusatzzahl erzeugen kann (vielleicht weiß ja jemand, wie man damit den Jackpot knacken kann ;-) )

;with n as

(

select 1 n

union all

select n + 1

from n

where

n < 49

),

z as

(

select 1 z

union all

select z + 1

from z

where

z < 10

)

,lotto as

(

select

n1.n as Zahl_1,

n2.n as Zahl_2,

n3.n as Zahl_3,

n4.n as Zahl_4,

n5.n as Zahl_5,

n6.n as Zahl_6,

z.z as Zusatzzahl

from n n1, n n2, n n3, n n4, n n5, n n6, z

where

n1.n not in (n2.n, n3.n, n4.n, n5.n, n6.n) and

n2.n not in (n1.n, n3.n, n4.n, n5.n, n6.n) and

n3.n not in (n1.n, n2.n, n4.n, n5.n, n6.n) and

n4.n not in (n1.n, n2.n, n3.n, n5.n, n6.n) and

n5.n not in (n1.n, n2.n, n3.n, n4.n, n6.n) and

n6.n not in (n1.n, n2.n, n3.n, n4.n, n5.n) and

n2.n > n1.n and

n3.n > n2.n and

n4.n > n3.n and

n5.n > n4.n and

n6.n > n5.n

)

select top(1) * -- aus 139838160 Zeilen

from lotto

order by

newid();

Viele Grüße

Christoph Ingenhaag