Problem poruszany w niniejszym wpisie można spotkać w przypadku budowania zapytań dla wszelkich interaktywnych tabelek. Zwykle pokazujemy tam szereg wybranych, np. wyszukiwaną frazą, rekordów z możliwością przełączania stron oraz decydowania ile wyników chcemy mieć na stronie. Ciekawiej robi się kiedy chcemy mieć dodatkowo możliwość sortowania po kolumnach (i to w obu kierunkach). Poniżej pokażę jak sobie z czymś takim poradzić za pomocną zwykłego, statycznego zapytania SQL, w którym jedynymi zmiennymi elementami będą przekazywane parametry.

Tradycyjnie, zaczniemy od przygotowania sobie odpowiedniego warsztatu pracy. Aby za dużo nie kombinować posłużymy się jedną tabelką, z której będziemy docelowo pobierać dane dla naszej listy. Tym razem pobawimy się kolekcją pracowników 🙂

create table dbo.pracownicy
(
	idPracownika int identity (1,1) primary key clustered,
	nazwisko nvarchar(100),
	imie nvarchar(30),
	rokUrodzenia int	
);

Dodajmy sobie jeszcze trochę danych, żeby było co wyszukiwać. Oczywiście wszystkie pojawiające się postacie są zmyślone a wszelkie podobieństwo do prawdziwych osób jest przypadkowe 😉

insert into dbo.pracownicy (imie, nazwisko, rokUrodzenia) values
(N'Anna', N'Kowalska', 1984),
(N'Maciej', N'Gozerski', 1990),
(N'Adam', N'Potorbicki', 1992),
(N'Justyna', N'Karęcka', 1987),
(N'Monika', N'Kornowicka', 1995),
(N'Bartłomiej', N'Nowak', 1992),
(N'Barbara', N'Mirągowska', 1989),
(N'Tomasz', N'Wormiński', 1994),
(N'Mikołaj', N'Sorębski', 1979),
(N'Patrycja', N'Dębska', 1997),
(N'Natalia', N'Janowicka', 2000),
(N'Szymon', N'Choręcki', 1996),
(N'Marzena', N'Żarska', 1992),
(N'Stefan', N'Lubawski', 2002),
(N'Jan', N'Wręgowski', 1999),
(N'Katarzyna', N'Sulimowska', 2003),
(N'Wojciech', N'Ordański', 1990);

Zdefiniujmy sobie teraz potrzeby naszej listy:

• możliwość wyszukiwania po początkowych literach nazwiska,
• możliwość wyświetlania konkretnej liczby wyników na raz,
• możliwość wybierania strony wyniku,
• możliwość sortowania po wybranej kolumnie,
• możliwość określania kierunku sortowania.

Dla ułatwienia dalszej pracy przedstawmy nasze potrzeby w formie zmiennych.

declare @fraza nvarchar(100) = N'';
declare @wynikowNaStronie int  = 10;
declare @strona int = 0; -- strony będziemy numerować od zera (to nie jest liczba omijanych rekordów!)
declare @sortowanieKolumna sysname = N'';
declare @sortowanieKierunek varchar(4) = '';

Oczywiście naszą frazę możemy definiować zupełnie inaczej. Na potrzeby tego wpisu posłużę się wyłącznie możliwością łatwego wyszukiwania po fragmencie nazwiska (ten wpis nie ma się skupiać na wyszukiwaniu więc nie chcę wchodzić w zbędne dygresje poświęcone wyszukiwaniu danych).

Typowe zapytanie dla wyszukania pierwszych dziesięciu osób, których nazwiska zaczynają się na literę S i posortowaną według nazwisk, miałoby postać mniej więcej taką (jak wiadomo każde zapytanie da się zapisać na wiele różnych sposobów) jak niżej. Celowo nie będę używał w niej powyższych zmiennych.

select *
from dbo.pracownicy
where nazwisko like 'S%'
order by nazwisko
offset 0 rows fetch next 10 rows only

Czego wynikiem byłaby następująca tabela

Spróbujmy zapisać to zapytanie, na ile się na razie da, z wykorzystaniem zdefiniowanych wcześniej zmiennych.

select *
from dbo.pracownicy
where nazwisko like concat(@fraza, '%')
order by nazwisko
offset @strona * @wynikowNaStronie rows fetch next @wynikowNaStronie rows only

Powstał problem jak sparametryzować sortowanie. W tej sytuacji najczęściej chyba stosuje się rozwiązaniem polegające na manipulacji samym zapytaniem gdzie budujemy zapytanie jako łańcuch znaków składanych z kilku elementów. Można to oczywiście zrobić w języku naszej aplikacji. Np. w języku PHP mogłoby to wyglądać tak jak poniżej.

$sortowanieKolumna = 'nazwisko';
$sortowanieKierunek = 'asc';
$query = <<<SQL
select *
from dbo.pracownicy
where nazwisko like concat(@fraza, '%')
order by $sortowanieKolumna $sortowanieKierunek
offset @strona * @wynikowNaStronie rows fetch next @wynikowNaStronie rows only
SQL;

Możemy to również zrobić w samym T‑SQL poprzez wykorzystanie zapytań dynamicznych np. w poniższy sposób.

declare @fraza nvarchar(100) = N'S';
declare @wynikowNaStronie int = 10;
declare @strona int = 0;
declare @sortowanieKolumna sysname = N'nazwisko';
declare @sortowanieKierunek varchar(4) = 'asc';

declare @query nvarchar(500) = concat(N'select * 
					from dbo.pracownicy 
					where nazwisko like concat(''', @fraza, N''', ''%'') 
					order by ', @sortowanieKolumna, N' ', @sortowanieKierunek, N' 
					offset ', @strona * @wynikowNaStronie, N'rows fetch next ', @wynikowNaStronie, N' rows only');

exec sp_sqlexec @query

Oczywiście na potrzeby ilustracyjne pominąłem zupełnie aspekt filtrowania danych. Warto jednak podkreślić, iż budowanie zapytań w powyższy sposób może być bardzo niebezpieczne i osobiście unikam tego jak ognia. Dlaczego? Ponieważ w ten sposób bardzo łatwo jest przemycić do zapytania dowolną treść (szczególnie jeśli nie sprawdzamy co jest przekazywane w tych zmiennych) i sprawić, że ta część aplikacji będzie podatna na ataki typu sql injection. Między innymi to właśnie owa niechęć do budowania zapytań w ten sposób skłoniła mnie do poszukiwania lepszego rozwiązania.

Może korzystając z chwili pokażę na przykładzie co złego mogłoby się stać.

Powyższe dynamiczne zapytanie SQL zwraca nam dokładnie ten sam wynik co wcześniej. Spróbujmy jednak zmodyfikować nieco wartość zmiennej @sortowanieKolumna.

declare @fraza nvarchar(100) = N'S';
declare @wynikowNaStronie int = 10;
declare @strona int = 0;
declare @sortowanieKolumna sysname = N'nazwisko asc; select * from dbo.pracownicy; select * from dbo.pracownicy order by nazwisko';
declare @sortowanieKierunek varchar(4) = 'asc';

declare @query nvarchar(500) = concat(N'select *
from dbo.pracownicy
where nazwisko like concat(''', @fraza, N''', ''%'')
order by ', @sortowanieKolumna, N' ', @sortowanieKierunek, N'
offset ', @strona * @wynikowNaStronie, N'rows fetch next ', @wynikowNaStronie, N' rows only');

exec sp_sqlexec @query

Naszym oczom ukaże się taki widok jak poniżej.

Jak widać w drugim zestawieniu otrzymaliśmy pełną zawartość tabeli pracowników. Dodam, że wcale nie musieliśmy pytać tam o dane pracowników. Mogliśmy np. zapytać o zawartość widoków systemowych co w połączeniu z częstym przypadkiem pracy aplikacji na koncie z uprawnieniami administracyjnymi dałoby atakującemu ogromną ilość wiedzy (np. odpytanie widoku sys.sql_modules dałoby nam kod wszystkich procedur i funkcji, natomiast widok sys.objects pokazałby nam listę widoków, tabel, procedur, itp.).

Jak w takim razie napisać to zapytanie bez budowania go w powyższy sposób? Otóż dyrektywa order by wcale nie musi mieć tak prostej formy jak prezentowana powyżej.

select *
from dbo.pracownicy
where nazwisko like concat(@fraza, '%')
order by case when @sortowanieKolumna = 'nazwisko' then nazwisko end
offset @strona * @wynikowNaStronie rows fetch next @wynikowNaStronie rows only

Jak widać możemy użyć konstrukcji warunkowych. W tym przypadku sortowanie po nazwisku odbędzie się tylko jeśli nazwa przekazanej kolumny sortowania będzie miała określoną nazwę. Warto podkreślić, że ta nazwa wcale nie musi odpowiadać nazwie kolumny w tabeli SQL! To może być dowolna inna wartość, która dla nas będzie oznaczać sortowanie po nazwisku (możemy tam np. przekazywać numer kolumny). Chodzi o to, że zmienna zawierająca nazwę kolumny nie buduje treści zapytania SQL i staje się zwykłym parametrem. Na potrzeby tego wpisu będę jednak stosował nazwy kolumn (aby nie wprowadzać zamieszania dodatkowymi definicjami).

Poniżej rozbudujemy sobie nasze zapytanie o sortowanie po innych kolumnach.

select *
from dbo.pracownicy
where nazwisko like concat(@fraza, '%')
order by
case when @sortowanieKolumna = 'idPracownika' then idPracownika end,
case when @sortowanieKolumna = 'nazwisko' then nazwisko end,
case when @sortowanieKolumna = 'imie' then imie end,
case when @sortowanieKolumna = 'rokUrodzenia' then rokUrodzenia end
offset @strona * @wynikowNaStronie rows fetch next @wynikowNaStronie rows only

Na koniec pozostało nam jeszcze uwzględnienie kierunku sortowania. Znacznik asc lub desc musimy umieścić na końcu każdego warunkowania, za słowem end i przed przecinkiem (jeśli występuje). Dlaczego powyższe zapytanie działa bez tego? Ponieważ w T‑SQL istnieje sortowanie domyślne i jest nim asc. Kolejnym ograniczeniem jest to, iż najwyraźniej nie da się (a przynajmniej mi nigdy się to nie udało) wprowadzić tego w formie warunkowej. Konieczne jest użycie bezpośrednio słów kluczowych asc i desc przez co część kodu trzeba będzie skopiować.

select *
from dbo.pracownicy
where nazwisko like concat(@fraza, '%')
order by
case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'idPracownika' then idPracownika end,
case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'nazwisko' then nazwisko end,
case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'imie' then imie end,
case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'rokUrodzenia' then rokUrodzenia end,
case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'idPracownika' then idPracownika end desc,
case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'nazwisko' then nazwisko end desc,
case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'imie' then imie end desc,
case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'rokUrodzenia' then rokUrodzenia end desc
offset @strona * @wynikowNaStronie rows fetch next @wynikowNaStronie rows only

Takie podeście ma jednak dadatkową zaletę – wartość przekazująca kierunek sortowania jest teraz sprawdzana dla obu dopuszczalnych wartości. Podanie innej niż asc i desc spowoduje wykonanie sortowania domyślnego (żaden z warunków nie będzie spełniony).

Nasze zapytanie możemy teraz ubrać sobie w procedurę, do której będziemy przekazywać wyłącznie niezbędne parametry.

create procedure wyszukajPracownika @fraza nvarchar(100), @wynikowNaStronie int, @strona int, @sortowanieKolumna sysname, @sortowanieKierunek varchar(4) as 
begin
	set nocount on;

	select *
	from dbo.pracownicy
	where nazwisko like concat(@fraza, '%')
	order by	
	case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'idPracownika' then idPracownika end,
	case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'nazwisko' then nazwisko end,
	case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'imie' then imie end,
	case when @sortowanieKierunek = 'asc' and @sortowanieKolumna = 'rokUrodzenia' then rokUrodzenia end,
	case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'idPracownika' then idPracownika end desc,
	case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'nazwisko' then nazwisko end desc,
	case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'imie' then imie end desc,
	case when @sortowanieKierunek = 'desc' and @sortowanieKolumna = 'rokUrodzenia' then rokUrodzenia end desc
	offset @strona * @wynikowNaStronie rows fetch next @wynikowNaStronie rows only
end;

Spróbujmy teraz wyszukać dziesięć najmłodszych osób w bazie naszych pracowników.

exec dbo.wyszukajPracownika '', 10, 0, N'rokUrodzenia', 'desc';

Efektem zapytania będzie w naszym przypadku taki widok.

Sam bardzo chętnie korzystam z tego rozwiązania więc może i komuś jeszcze przypadnie do gustu 🙂