Wir bauen einen Filter

Warum bauen wir einen Filter in T-SQL?

Manchmal möchte man im Backend eine Stored Procedure haben die Daten an eine Anwendung wie beispielsweise ein ASP.NET Core Webanwendung gefiltert zurückliefert. In der Regel wird so ein Filter, wenn es nicht gerade eine Unzahl an Werten sind die gefiltert werden sollen in der Benutzeroberfläche als Combobox dargestellt. Um einen möglichst komfortablen Filter zu haben müssen drei Stati abgedeckt werden:

  • In der Combobox ist nichts ausgewählt, d.h. der Filter ist nicht aktiv, es werden alle Datensätze zurückgegeben
  • Es ist ein Wert ausgewählt, d.h. es müssen alle Daten zurückgegeben werden bei denen im entsprechenden Feld der ausgewählte Wert enthalten ist
  • Es sind mehrere Werte ausgewählt, d.h. es müssen alle Datensätze ausgewählt werden bei denen im spezifischen Feld einer der angegebenen Werte enthalten ist. 

Damit es nicht zu einfach wird haben wir in unserer Anwendung möglicherweise mehr als einen Filter, d.h. es müssen mehere Felder entsprechend der übergebenen Werte gefiltert werden. 

Wie sieht unser Setup aus?

Wie immer arbeiten wir mit der Wide World Importers Standard-Datenbank, genauer mit der Tabelle Application.Countries. Diese Tabelle enthält eine Liste mit Ländern die auch noch einem Kontinent zugeordnet sind, die wiederum einer Region zugeordnet sind. Damit können wir doch arbeiten.

SELECT
     CountryID
   , CountryName
   , FormalName
   , IsoAlpha3Code
   , IsoNumericCode
   , CountryType
   , LatestRecordedPopulation
   , Continent
   , Region
   , Subregion
   , Border
   , LastEditedBy
   , ValidFrom
   , ValidTo
FROM Application.Countries;

Einen sehr einfachen Filter auf ein Feld zu bauen ist keine große Aufgabe, das ist schlicht und einfach eine WHERE Abfrage im Select Befehl. Möchten wir beispielsweise die zurückgegebenen Länder auf alle Länder filtern die in der Region Americas sind können wir dazu den folgenden Befehl nutzen:

DECLARE @pRegion nvarchar(30) = N'Americas';

SELECT
	  CountryName
	, Continent
	, Region
	, Subregion
FROM  Application.Countries
WHERE Region = @pRegion;

So weit, so einfach. Etwas kniffliger wird es, wenn wir diesen Filter nun ein- und ausschaltbar machen wollen. Wenn wir gar nicht filtern wollen dann können wir ja nicht irgendeinen Wert übergeben. Die Lösung ist aber eigentlich ganz einfach: Als “kein Filter” Wert übergeben wir einfach den Wert NULL. Wenn dieser Wert kommt soll die WHERE Klausel einfach das zu filternde Feld mit sich selbst vergleichen. Ein Wert mit sich selbst verglichen liefert immer das Ergebnis TRUE zurück und somit ist die Bedingung immer erfüllt. Ein solches Verhalten können wir durch die Funktion ISNULL erzielen. Was ISNULL genau macht findet ihr hier. Kurz gesagt: ISNULL überprüft ob der erste Parameter NULL ist. Ist das der Fall wird der Wert des zweiten Parameters zurückgegeben, ansonsten der des ersten. Das ist doch genau das, was wir brauchen. Der Trick ist, dass wir die Funktion ISNULL nicht auf die Spalte sondern auf den übergebenen Parameter anwenden. Übergeben wir einen Wert wie im Beispiel zuvor, so funktioniert der Filter genau wie zuvor:

DECLARE @pRegion nvarchar(30) = N'Americas';

SELECT
	  CountryName
	, Continent
	, Region
	, Subregion
FROM  Application.Countries
WHERE Region = ISNULL(@pRegion, Region);

Setzen wir den Parameter allerdings auf NULL so wird nicht mehr gefiltert. Wir bekommen 190 Datensätze zurückgeliefert, genau wie beim allerersten SQL Befehl ganz oben:

DECLARE @pRegion nvarchar(30) = NULL;

SELECT
	  CountryName
	, Continent
	, Region
	, Subregion
FROM  Application.Countries
WHERE Region = ISNULL(@pRegion, Region);

Was für einen Parameter und eine Spalte funktioniert funktioniert natürlich genau so für mehr als eine Spalte und einen Parameter. Im Beispiel haben wir alle Bedingungen mit einem AND miteinander verknüpft, weil das die von uns gewünschte Funktionalität ist: Je mehr Filter wir in den Comboboxen setzen desto mehr Werte werden herausgefiltert. Ohne jegliche Filterung liefert uns der Befehl wieder 190 Datensätze zurück.

DECLARE
    @pRegion nvarchar(30) = NULL
  , @pSubRegion nvarchar(30) = NULL
  , @pContinent nvarchar(30) = NULL
  , @pCountryName nvarchar(30) = NULL;

SELECT
	  CountryName
	, Continent
	, Region
	, Subregion
FROM  Application.Countries
WHERE Region = ISNULL (@pRegion, Region)
	  AND Subregion = ISNULL (@pSubRegion, Subregion)
	  AND Continent = ISNULL (@pContinent, Continent)
	  AND CountryName = ISNULL (@pCountryName, CountryName);

Wenn wir nun Filterwerte in die Parameter setzen wird entsprechend gefiltert. Da die Felder ContinentRegion und Subregion eine Hierarchie bilden ist egal ob wir auf die einzelnen Parameter filtern oder nur in Subregion auf Caribbean. Hätten wir aber Felder die keine Korelation haben wie in diesem Beispiel würde das auch funktionieren.

DECLARE
    @pRegion nvarchar(30) = 'Americas'
  , @pSubRegion nvarchar(30) = 'Caribbean'
  , @pContinent nvarchar(30) = 'North America'
  , @pCountryName nvarchar(30) = NULL;

SELECT
	  CountryName
	, Continent
	, Region
	, Subregion
FROM  Application.Countries
WHERE Region = ISNULL (@pRegion, Region)
	  AND Subregion = ISNULL (@pSubRegion, Subregion)
	  AND Continent = ISNULL (@pContinent, Continent)
	  AND CountryName = ISNULL (@pCountryName, CountryName);

So, damit sind wir mit unserem Filter fast soweit durch, die einzige Sache die jetzt noch fehlt ist, dass wir auch mehrere Werte in einem Feld filtern können. Um diese Funktion umzusetzen nutzen wir die Funktion TRIM und STRING_SPLIT in Kombination mit dem Operator IN. Das TRIM wird verwendet um eventuell vorhandene Leerzeichen am Anfang und am Ende des Textes zu löschen, mit STRING_SPLIT kann man einen Text der mit einem Trennzeichen getrennt wird in einzelne Zeilen eines Datasets verwandeln. Zu STRING_SPLIT hier ein kleines Beispiel:

SELECT
   value
FROM STRING_SPLIT('Metallica,Megadeth,Slayer,Anthrax',',');

WICHTIG: Die Funktion STRING_SPLIT gibt es erst seit der SQL Server Version 2016.

Das können wir uns nun für unseren Filter zunutze machen. Die Verbindung zu dem bisherigen Filter stellen wir über ein OR her. Das Beispiel liefert alle Länder in Caribbean und Central America zurück:

DECLARE
    @pRegion nvarchar(30) = N'Americas'
  , @pSubRegion nvarchar(30) = N'Caribbean,Central America'
  , @pContinent nvarchar(30) = N'North America'
  , @pCountryName nvarchar(30) = NULL;

SELECT
	  CountryName
	, Continent
	, Region
	, Subregion
FROM  Application.Countries
WHERE (
		  Region IN (
						SELECT
							 TRIM (value)
						FROM STRING_SPLIT(@pRegion, ',')
					)
		  OR Region = ISNULL (@pRegion, Region)
	  )
	  AND (
			  Subregion IN (
							   SELECT
									TRIM (value)
							   FROM STRING_SPLIT(@pSubRegion, ',')
						   )
			  OR Subregion = ISNULL (@pSubRegion, Subregion)
		  )
	  AND (
			  Continent IN (
							   SELECT
									TRIM (value)
							   FROM STRING_SPLIT(@pContinent, ',')
						   )
			  OR Continent = ISNULL (@pContinent, Continent)
		  )
	  AND (
			  CountryName IN (
								 SELECT
									  TRIM (value)
								 FROM STRING_SPLIT(@pCountryName, ',')
							 )
			  OR CountryName = ISNULL (@pCountryName, CountryName)
		  );

Sicherlich sind die Daten nicht unbedingt die geeignetsten Daten für dieses Beispiel, da es zwischen den Daten eine Abhängigkeit gibt aber ich denke dass zumindest die Idee wie das mit den Filtern funktioniert klar wird. Um das Beispiel abzuschliessen giessen wir das ganze jetzt noch in eine Stored Procedure die dann ganz bequem von der Frontend-Anwendung aufgerufen werden kann:

IF OBJECT_ID ('Application.filter_countries', 'P') IS NOT NULL
	DROP PROCEDURE Application.filter_countries;
GO

CREATE PROCEDURE Application.filter_countries
	@pRegion nvarchar(30) NULL
  , @pSubRegion nvarchar(30) NULL
  , @pContinent nvarchar(30) NULL
  , @pCountryName nvarchar(30) NULL
AS
SET NOCOUNT ON;

	BEGIN
		SELECT
			  CountryName
			, Continent
			, Region
			, Subregion
		FROM  Application.Countries
		WHERE (
				  Region IN (
								SELECT
									 TRIM (value)
								FROM STRING_SPLIT(@pRegion, ',')
							)
				  OR Region = ISNULL (@pRegion, Region)
			  )
			  AND (
					  Subregion IN (
									   SELECT
											TRIM (value)
									   FROM STRING_SPLIT(@pSubRegion, ',')
								   )
					  OR Subregion = ISNULL (@pSubRegion, Subregion)
				  )
			  AND (
					  Continent IN (
									   SELECT
											TRIM (value)
									   FROM STRING_SPLIT(@pContinent, ',')
								   )
					  OR Continent = ISNULL (@pContinent, Continent)
				  )
			  AND (
					  CountryName IN (
										 SELECT
											  TRIM (value)
										 FROM STRING_SPLIT(@pCountryName, ',')
									 )
					  OR CountryName = ISNULL (@pCountryName, CountryName)
				  );


	END;

Diese Prozedur kann nun ganz einfach aufgerufen werden:

EXECUTE Application.filter_countries
    @pRegion = N'Americas'
  , @pSubRegion = N'Caribbean,Central America'
  , @pContinent = N'North America'
  , @pCountryName = NULL;
GO

Referenzen

Ninja-Notebooks @ GitHub

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.