Where in mit dynamischer Liste

[tini]

Hallo Ihr,

weiß vielleicht jemand, wo mein Fehler liegt?
Ziel ist es eine T-SQL Funktion zu definieren, welcher ich eine Liste übergeben kann, um Sie in der Where-Klausel zur Einschränkung der Auswahl verwenden.

Ziel Werte dieser Abfrage:
select * from ... Where type in ('x','y')

Ansatz der Funktion:

create funktion(
    @list varchar(255)
)...

Declare @list2 varchar(255)
    Set @list2 = replace(replace(@list,'/',char(44)),'.',char(39))
... select * from ... Where type in (@list2)

Anwendung der Funktion mit:
select funktion('.x./.y.')

Problem:
Die Anwendung der Funktion bringt den Wert null, ob wohl sie einen Wert für die Abfrage liefern sollte.

Was muss ich ändern,um mit dieser Funktion das gewünschte Ergebnis zu erhalten?

Vielen Dank im Voraus!
Grüße Tini

[Vinzenz Mai]

Hallo,

Ziel ist es eine T-SQL Funktion zu definieren, welcher ich eine Liste übergeben kann, um Sie in der Where-Klausel zur Einschränkung der Auswahl verwenden.

Declare @list2 varchar(255)
    Set @list2 = replace(replace(@list,'/',char(44)),'.',char(39))
... select * from ... Where type in (@list2)

verstehe ich Dich richtig? Du möchtest eine Zeichenfolge zusammenbauen, die eine T-SQL-Anweisung darstellt und diese ausführen.

Was muss ich ändern,um mit dieser Funktion das gewünschte Ergebnis zu erhalten?

Du könntest die resultierende Zeichenfolge mit EXECUTE (kurz EXEC) ausführen, siehe z.B. </archiv/2006/3/t125385/#m808888>. Bei SQL-Sever 2005 sind übrigens Zeichenfolgen bis zu 2GB zugelassen. Beachte die Hinweise zu SQL-Injection.

Freundliche Grüße

Vinzenz

[Tini]

Hallo Vinzenz,

Danke für deine schnelle Antwort. Jedoch suche ich eine Möglichkeit wie ich die Liste für eine Where in-Klausel flexibel in eine komplexe Abfrage die in einer Funktion definiert ist einzufügen. Da im Moment die Funktion für jeden Parameter einzeln aufgerufen wird.

Beispiel: select funktion('a')+funktion('l')+...+funktion('x')

Um die select-Aufrufe zu minimieren währe es praktisch, wenn ich die funktion wie folgt aufrufen könnte: funktion('.a./.l./. ... ./.x.').
Da sich die Parameter der Funktion ändern je nach bedarf kann die Where in-Klausel keine feste Liste enthalten.
Sogesehen wäre es supper, wenn dies irgendwie möglich ist.

Bei dem unten stehenden Versuch verstehe ich nicht wieso die Variable list2 nicht durch 'x','y' in der Klammer ersetzt wir und somit die Where in Klausel = Where in('x','y') entspricht. Jedoch ist das aus irgendeinem Grund nicht der Fall und somit erhalte ich null als Abfrageergebnis?

Weiß du oder jemand anderes warum, das nicht so funktioniert?

»»     Declare @list2 varchar(255)
»»     Set @list2 = replace(replace(@list,'/',char(44)),'.',char(39))
»» ... select * from ... Where type in (@list2)

Beste Grüße,
Tini

[Vinzenz Mai]

Hallo,

Danke für deine schnelle Antwort. Jedoch suche ich eine Möglichkeit wie ich die Liste für eine Where in-Klausel flexibel in eine komplexe Abfrage die in einer Funktion definiert ist einzufügen.

Wenn Du aus dem Wert, der in Deiner Variable steht, eine Liste von Werten machen willst, so kannst Du dies über das (riskante) dynamische Erstellen einer SQL-Anweisung tun.

Um die select-Aufrufe zu minimieren währe es praktisch, wenn ich die funktion wie folgt aufrufen könnte: funktion('.a./.l./. ... ./.x.').

Das ist genau ein Wert, der übergeben wird. Nicht mehr, nicht weniger.

Da sich die Parameter der Funktion ändern je nach bedarf kann die Where in-Klausel keine feste Liste enthalten.

dann musst Du Dein Statement dynamisch zusammenbauen und mit EXEC ausführen, wie ich bereits schrieb.

Bei dem unten stehenden Versuch verstehe ich nicht wieso die Variable list2 nicht durch 'x','y' in der Klammer ersetzt wir und somit die Where in Klausel = Where in('x','y') entspricht.

Nein, das ist nicht der Fall, es sieht so aus, dass die Liste in den Klammern hinter IN immer noch genau einen einzigen Wert enthält, den Wert

'x','y'

und nicht etwa eine kommaseparierte Liste von zwei Werten.

Jedoch ist das aus irgendeinem Grund nicht der Fall und somit erhalte ich null als Abfrageergebnis?

Völlig logisch. Das ist auch gut so. @list2 ist *ein* Wert, egal wie Du Deine Variable nennst, egal welchen Inhalt sie hat.

Zur Erläuterung und zum besseren Verständnis (Code kannst Du im SQL-Server Management-Studio ausführen lassen):

-- legen wir uns zwei Variablen mit irreführenden Namen an.  
-- Es handelt sich in beiden Fällen um skalare Werte, nicht um Listen.  
DECLARE @list VARCHAR(255);  
DECLARE @list2 VARCHAR(255);  
  
SET @list = '.x./.y.';  -- Liste enthält einen Wert, die angegebene Zeichenkette  
SELECT @list spalte;    -- die Zeichenkette wird ausgegeben  
SET @list2 = replace (replace(@list,'/',char(44)),'.',char(39));  
SELECT  
    @list original,     -- die Originalzeichenkette  
    @list2 ergebnis;    -- ein einzelner Wert mit einer Zeichenkette, die Zeichen wie  
                        -- das einfache Anführungszeichen und das Komma enthält.  
                        -- Ein solcher Wert ist zulässig.  
  
SELECT 1 WHERE 'x' IN (@list2);           -- Ergebnis: leere Menge  
SELECT 1 WHERE 'y' IN (@list2);           -- Ergebnis: leere Menge  
SELECT 1 WHERE '''x'',''y''' IN (@list2); -- Ergebnis: 1  
-- weil in einer Zeichenkette das einfache Anführungszeicehn in T-SQL  
-- verdoppelt werden muss.

Nochmals: Mochtest Du aus Deiner Variablen eine kommaseparierte Liste für den IN-Operator erzeugen, so musst Du das SQL-Statement dynamisch zusammenstellen und anschließend mit EXEC(UTE) ausführen. Das geht! Es ist nicht notwendig, Kommas und Anführungszeichen dafür vorher in andere Trennzeichen umzuwandeln und diese Umwandlung anschließend wieder rückgängig zu machen. Achte jedoch darauf, dass einfache Anführungszeichen in den späteren Werten selbst verdoppelt werden müssen.

Freundliche Grüße

Vinzenz