Why do we build a filter in T-SQL?
Sometimes you want to have a stored procedure in the backend that returns filtered data to an application such as an ASP.NET Core web application. Usually such a filter, if it is not a myriad of values to be filtered, is displayed in the user interface as a combo box. In order to have a filter that is as comfortable as possible, three states have to be covered:
- Nothing is selected in the combo box, i.e. the filter is not active, all records are returned.
- One value is selected, i.e. all data must be returned where the selected value is contained in the corresponding field.
- Multiple values are selected, i.e. all records must be selected where one of the specified values is contained in the specific field.
DaTo make it not too easy we might have more than one filter in our application, i.e. several fields have to be filtered according to the given values.
What does our setup look like?
As usual, we work with the Wide World Importers standard database, more precisely with the Application.Countries table. This table contains a list of countries that are also assigned to a continent, which in turn are assigned to a region. We can work with that after all.
SELECT
CountryID
, CountryName
, FormalName
, IsoAlpha3Code
, IsoNumericCode
, CountryType
, LatestRecordedPopulation
, Continent
, Region
, Subregion
, Border
, LastEditedBy
, ValidFrom
, ValidTo
FROM Application.Countries;
Building a very simple filter on a field is not a big task, this is simply a WHERE query in the Select command. For example, if we want to filter the returned countries to all countries that are in the Americas region we can use the following command:
DECLARE @pRegion nvarchar(30) = N'Americas'; SELECT CountryName , Continent , Region , Subregion FROM Application.Countries WHERE Region = @pRegion;
So far, so simple. It gets a bit trickier when we want to make this filter switchable on and off. If we don’t want to filter at all, then we can’t just pass any value. The solution is actually quite simple: As “no filter” value we simply pass the value NULL. When this value is passed, the WHERE clause should simply compare the field to be filtered with itself. A value compared with itself always returns the result TRUE and thus the condition is always fulfilled. We can achieve such a behavior by using the function ISNULL. What ISNULL does exactly you can find here. In short: ISNULL checks if the first parameter is NULL. If it is, the value of the second parameter is returned, otherwise the value of the first parameter is returned. This is exactly what we need. The trick is that we don’t apply the ISNULL function to the column but to the passed parameter. If we pass a value like in the example before, the filter works exactly as before:
DECLARE @pRegion nvarchar(30) = N'Americas'; SELECT CountryName , Continent , Region , Subregion FROM Application.Countries WHERE Region = ISNULL(@pRegion, Region);
However, if we set the parameter to NULL, the filtering will stop. We get 190 records returned, just like the very first SQL command above:
DECLARE @pRegion nvarchar(30) = NULL; SELECT CountryName , Continent , Region , Subregion FROM Application.Countries WHERE Region = ISNULL(@pRegion, Region);
What works for one parameter and one column of course works for more than one column and one parameter. In the example, we have linked all conditions with an AND, because that is the functionality we want: The more filters we set in the combo boxes, the more values are filtered out. Without any filtering, the command returns 190 records.
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);
If we now set filter values in the parameters, the filtering will be done accordingly. Since the fields Continent, Region and Subregion form a hierarchy, it does not matter if we filter on the individual parameters or only in Subregion on Caribbean. However, if we had fields that have no correlation, as in this example, this would also work.
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, we are almost done with our filter, the only thing that is missing now is that we can filter multiple values in one field. To implement this function we use the TRIM function and STRING_SPLIT in combination with the IN operator. The TRIM is used to delete possibly existing blanks at the beginning and at the end of the text, with STRING_SPLIT we can transform a text which is separated with a separator into single lines of a dataset. To STRING_SPLIT here a small example:
SELECT
value
FROM STRING_SPLIT('Metallica,Megadeth,Slayer,Anthrax',',');
IMPORTANT: The function STRING_SPLIT exists only since SQL Server version 2016.
We can now make use of this for our filter. We connect to the previous filter using an OR. The example returns all countries in Caribbean and Central America:
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)
);
Surely the data is not the most suitable data for this example, since there is a dependency between the data, but I think that at least the idea of how this works with the filters becomes clear. To finish the example we will now cast the whole thing into a stored procedure that can then be conveniently called from the frontend application:
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;
This procedure can now be called quite easily:
EXECUTE Application.filter_countries
@pRegion = N'Americas'
, @pSubRegion = N'Caribbean,Central America'
, @pContinent = N'North America'
, @pCountryName = NULL;
GO
