Zusamengesetzten String an Stored Procedure übergeben

[ruewue]

Hallo Zusammen,

ich konnte leider keine Antwort auf meine Frage hier im Forum finden. Mag sein das ich da was übersehen habe. Aber ich stell sie nun mal den Spezialisten.

Ich habe eine stored Procedure die auch sehr gut läuft. An diese möchte ich nun eine parameter übergeben, der wie folgt in der SP verarbeitet wird.

...
WHERE xValue IN (@Value)
...

Wie kann ich nun @Value in meiner EXEC anlegen, so das dort " 'XA10', 'XB10', 'XC10' " übergeben wird? Mit eine Wert " 'XA10'" z.B. funktioniert es.

Danke schon mal für die Antwort

 

[Tommi]

Hallo ruewue,

die IN-Funktion erwartet hier eine Aufzählung von Elementen. Um dies über Parameter zu nutzen müsstest du einen Multivalue-Datentyp zur Verfügung haben.
Einen solchen gibt as standardmäßig für T-SQL leider nicht. So etwas müsste man explizit programmieren (z.B. über CLR).
Es gibt aber noch andere Möglichkeiten um die abzubilden.


Die einfachste Methode, die man hier nutzen kann, die aber nicht gerade ein Performance-Hit ist, wäre ein String-Vergleich ind folgender Form:

WHERE CHARINDEX(CAST (xValue as varchar(25))+';' , @Value+';')>0

Die nächste Möglichkeit wäre es, aus dem übergebenen String eine temporäre Tabelle zu erstellen.
Dazu musst du den über den Parameter übergebenen String aufsplitten und zeilenweise die Werte in diese temporäre Tabelle schreiben.
In der WHERE-Klausel der Abfrage kannst du dann mit einem Sub-Select ebenfalls diese IN-Funktion verwenden oder du Joinst die Tabelle einfach hinzu.

WHERE xValue IN (SELECT ValueData FROM #tempTable)

oder

...
FROM dbo.tabelle A
INNER JOIN #tempTable B
     ON A.xValue=B.ValueData

Anstatt eines einfachen Strings kannst du natürlich auch einen xml-Datentyp als Parameter übergeben, das wirkt eleganter.
Diesen Datentyp kann man ebenfalls mit SELECTs abfragen. Hier müsste ich aber auch die genaue Syntax noch einmal raussuchen.
Hier hilft aber dann evtl. der folgende Link:

http://msdn.microsoft.com/de-de/library/ms190936(v=sql.90).aspx

Es ist aber sicher immer einfacher einen normalen String statt eines XML-Konstrukts zu erstellen.

Viele Grüße,
Tommi

 

[ukulele]

Man muss natürlich aus dem eizelnen, zusammengesetzten String nicht zwingend wieder eine Tabelle machen. In deinem Fall würde ein

WHERE xValue LIKE '%' + @Value + '%'

genügen. Die Performance ist in der Tat nicht die beste aber kein Beinbruch wenn das nicht alle paar Sekunden gemacht wird. Und du musst sicher stellen, das dein übergebener String auch immer alle Werte aufgenommen und nicht abgeschnitten hat.

 

[Tommi]

Hallo,

@ukulele: die LIKE-Funktion würde in diesem Falle nicht funktionieren. Wenn ich das richtig verstannden habe, soll im Parameter eine durch einen Seperator getrennte Auflistung von möglichen Werten in einem String gemacht werden.
In dem Parameter-String wären also mehrere Werte der Spalte xValue angegeben. Ein kleines Beispiel zur Veranschaulichung, da ich mich mit einer klaren Erklärung hier ein wenig schwer tue:

In einer Tabelle gibt es eine Spalte Namen. Hier sind Einträge drin wie 'Herrmann', Herrmanns', Herr', 'Geherren' , 'Herrendorf', 'Frei', 'Freimann', 'Freihafen'.
Im ersten Fall soll nun nur der Datensatz mit dem Namen 'Herr' , im zweiten Fall sollen die Einfräge für 'Herr' und 'Frei' ausgegeben werden.

Für Fall 1 würde die von dir angegebene LIKE-Funktion funktionieren, wenn man den Seperator berücksichtigt, für den ich hier ein Semikolon annehme:

...
WHERE xValue+';' LIKE  '%' + @Param + ';%'

Die LIKE-Funktion funktioniert aber nicht mehr, wenn der Parameter so übergeben wird: @Param='Herr;Frei'
Dann zieht nur noch der Vergleich mit der CHARINDEX-Funktion.

Viele Grüße,
Tommi

 

[ukulele]

Sry ich habs verdreht.

WHERE @Value LIKE '%' + xValue + '%'

Ich würde sagen xValue ist immer ein einzelner Wert (zumindest ist im ursprünglichen Post nichts anderes beschrieben) und den kann ich mit LIKE in einer beliebig langen Parameterliste suchen, natürlich immer vorrausgesetzt es handelt sich nur um einen Einzelwert.

 

[Tommi]

@ukulele: stimmt, so geht´s prinzipiell. Trotzdem sollte man dann den Seperator noch mit berücksichtigen. Man darf hier auch nicht außer Acht lassen, dass ein LIKE immer nur ähnliche, aber nicht gleiche Werte ermittelt.
Aus meiner Sicht ist ein LIKE für die vom Thread-Ersteller beschriebene Anwendung zu ungenau. Aber ich bin an der Stelle ganz klar ein echter Erbsenzähler (-> beruflich bedingt ) und hängt ja auch immer von der Anforderung ab.

Viele Grüße,
Tommi

 

[Tommi]

Hi,

wenn ich in Bezug auf Genauigkeit mal genauer drüber nachdenke, ist auch mein erster Vorschlag mit dem CHARINDEX in dieser Form zu ungenau.
Wenn man in der im von mir angeführten Beispiel noch zusätzlich den Namen 'Gutherr' einträgt und man den Eintrag für den Namen 'Herr' erhalten will, reicht mein Vorschlag auch noch nicht aus.
Man muss hier den String-Seperator am Anfang und am Ende eines Suchbegriffs berücksichtigen:

...
WHERE CHARINDEX( ';' + xValue + ';' , ';' + @Param + ';') > 0

Nur so erhält man einen 1:1 - Vergleich. Ich glaube, so war die Vorgabe des Thread-Erstellers? Oder hab ich das falsch verstanden?
Bei einer Abfrage über IN habe ich sofort an einen solchen 1:1 - Vergleich gedacht.

Viele Grüße,
Tommi

 

[ukulele]

Das kann wohl nur der Thread-Ersteller beantworten.

Sollte eine solche Genauigkeit gewünscht sein würde sich der Aufwand lohnen um eventuell auch vorher schon eine Tabelle mit den Werten zu füllen und erst dann die Prozedur aufzurufen. Die Essenz ist ja eigentlich nur, das es in T-SQL keine standartisierte Möglichkeit gibt einen Datentypen mit mehreren Werten zu befüllen.

 

[Tommi]

Hi,

ich wollte hier noch einmal etwas ergänzen. Ich habe zu diesem Thema nämlich auch durchaus einen konkreten Fall:

In einem Bericht auf dem Report Server sollen Umsätze von Kunden ausgewertet werden. Die Kunden können über einen Mehrfach-Parameter für die Ausewrtung ausgewählt werden.
Wer das nicht kenn, hier kurz erklärt: in einer Liste werden alle auswählbaren Kunden angeueigt. Über ein Häkchen, das an einen Kunden angehängt ist, kann dieser an- oder abgewählt werden.
Der Berichtsserver verwaltet diese Mehrfach-Parameter als Array. In diesem Array stehen dann die IDs der ausgewählten Kunden.
Hier kommt nun das Dilemma: die Auswertung erfolgt auf einem MS SQL-Server, der ja bekanntlich keine mehrfachen Einträge in einem Parameter verwalten kann.

Hier helfe ich mir nun, indem ich das Array, das im Berichtparameter verwaltet wird, zu einem gesamten String joine.
In deisem String werden alle Kunden-IDs mit Semikolon getrenn hintereinander aufgeführt.
Diesen String übergebe ich an die Prozedur, mit der die Datenabfrage vorgenommen wird.

Eine ID kann ich aber nicht mit einem LIKE vergleichen. Hier benötige ich eine eindeutige 1:1 Beziehung.

Da ich persönlich nun ein fauler Hund bin und dazu auch noch sehr schnell die Übersicht bei komplexeren String-Operationen verliere, habe ich mir mit einer Umwandlung in XML geholfen.

Im Folgenden seht ihr nun meinen Code (oder Kot?) mit dem ich eine zusätzliche Tabelle für eine direkten Vergleich zu Kundenauswahl bereitstelle:

DECLARE @parKD_ID varchar(max)
SET @parKD_ID='1000;2000;3000;4000'
 
-- Deklaration der Vergleichs-Tabelle
CREATE TABLE #ID
( NR int IDENTITY(1,1),
 KD_ID int
)

-- Umwandlung des Parameter-Strings in XML-Format
DECLARE @X as xml
WHILE RIGHT(@parKD_ID,1)=';'
BEGIN
 SET @parKD_ID = LEFT(@parKD_ID, LEN(@parKD_ID)-1)
END
 
SET @parKD_ID='<HKey><KND>'+REPLACE(@parKD_ID,';','</KND><KND>')+'</KND></HKey>'
SET @X = CAST(@parKD_ID as xml)
 
-- Eintrag der einzelnen IDs in die Vergleichs-Tabelle
INSERT INTO #ID (KD_ID)
SELECT result.value('/KND[1]', 'int')
FROM (
 SELECT T.c.query('.') AS result
 FROM @x.nodes('/HKey/KND') T(c)) X
 
-- Dann der Join der Vergleichs-Tabelle auf die Kunden-Tabelle, um nur die gewählten Kunden auszuwerten
SELECT ...
FROM dbo.Kunden K
INNER JOIN #ID I
 ON K.KD_ID=I.KD_ID
 
-- Beispiel-Ende
DROP TABLE #ID

Ich gebe zu, die XML-Geschichte sieht zunächst etwas undurchsichtig aus, ist aber (aus meiner Sicht) einfacher als eine Schleife zu programmieren,
mit der dann der String mit Kundennummern bearbeitet wird. Ach ja - und etwas schneller ist es auch, gerade wil keine Schleife drin ist.

Viele Grüße,
Tommi