Třídění podle různých sloupců pomocí CASE v MS SQL

1. dubna 2005

•

Umožnit uživateli zobrazit údaje setříděné podle různých sloupců a různým směrem je velmi žádaná funkce. V tomto článku si ukážeme, jak chytrým využitím klausule CASE můžeme parametricky volit jak sloupec, podle kterého třídit, tak i směr setřídění.

Již dříve jsme psali o třídění v ovládacím prvku DataGrid, které však údaje setřídí až na straně klienta, tedy mimo zdroj dat. V řadě případů, zvláště při větších objemech dat, je výhodnější získat data v potřebné podobě již ze zdroje, v našem případě z SQL serveru. Nepříliš elegantním řešením je skládat potřebný dotaz jako řetězec, pro sloupce, podle kterého chceme údaje třídit, bychom sestavili jiný dotaz. Toto není dobré řešení. Krom toho, že se zde zbavujeme možnosti využít uložených procedur a jejich výkonu, si přivodíme i možný bezpečnostní problém, a to hned dvojího druhu – ke každé tabulce, nad kterou budeme přímo provádět dotaz, musí mít klient přiděleno oprávnění, a také se může při chybě v aplikaci spustit úplně jiný dotaz, než jsme původně zamýšleli.

V našem řešení přesuneme logiku rozhodování do uložené procedury prostřednictvím parametrů. Využijeme klíčového slova CASE – v případě, že nedojde ke shodě parametru s danými hodnotami, je výsledek výrazu NULL. Toho můžeme chytře využít při řazení záznamů pomocí ORDER BY – pokud je hodnota daného sloupce NULL, je při řazení ignorována. S pomocí CASE tedy snadno napíšeme výraz, kdy se bude brát v potaz pouze vybraný sloupec, jakoby se prováděl dotaz například této podoby:

— třídí pouze podle Column1
SELECT Column1 FROM Table ORDER BY Column1, NULL
— třídí pouze podle Column2
SELECT Column1 FROM Table ORDER BY NULL, Column2

Prakticky může fungující parametrizovaný dotaz vypadat takto:

SELECT [Name]
FROM
    [dbo].[Table]
ORDER BY
    CASE WHEN @SortBy = ‚Column1‘ THEN [Name] END,
    CASE WHEN @SortBy = ‚Column2‘ THEN [SurName] END

Parametrizované výrazy pro část WHEN v klauzuli CASE nám umožní nejen třídit podle zvoleného sloupce, ale také volit směr setřídění – tak můžeme napsat kompletní proceduru, která poskytne přímo ze zdroje data seřazená přesně tak, jak je potřebujeme k zobrazení pro klienta. Výraz zmodifikujeme tak, aby se patřičný sloupec pro ORDER BY uplatnil s doplňkem DESC nebo bez něj:

— třídí podle Column1 vzestupně
SELECT Column1 FROM Table ORDER BY Column1, NULL DESC, NULL, NULL DESC
— třídí podle Column1 sestupně
SELECT Column1 FROM Table ORDER BY NULL, Column1 DESC, NULL, NULL DESC
— třídí podle Column2 sestupně
SELECT Column1 FROM Table ORDER BY NULL, NULL DESC, NULL, Column2 DESC

Do procedury proto zahrneme jak parametr určující podle čeho třídit, tak parametr určující směr setřídění. I když se kód zdá příliš upovídaný, díky tomu, že jde o kód uložené procedury, bude její prováděcí plán při kompilaci ještě optimalizován. Současně je takto zapsaný kód bezpečný – ačkoli je část dotazu parametrizována, nemůže dojít k tomu, že by byl vykonán nechtěný podvržený dotaz (například DELETE).

Kompletní kód procedury může vypadat třeba takto:

CREATE PROCEDURE [dbo].[PropertiesSortBy]
(
    @SortBy VARCHAR(128),
    @DescSortDir BIT
)
AS
    SET NOCOUNT ON
    SELECT
        [Id], [Name], [Value]
    FROM
        [dbo].[Properties]
    ORDER BY
        CASE WHEN (@DescSortDir = 1 AND @SortBy = ‚Editor‘)THEN [EditorRank] END,
        CASE WHEN (@DescSortDir = 0 AND @SortBy = ‚Editor‘)THEN [EditorRank] END DESC,
        CASE WHEN (@DescSortDir = 1 AND @SortBy = ‚Service‘)THEN [ServiceRank] END,
        CASE WHEN (@DescSortDir = 0 AND @SortBy = ‚Service‘)THEN [ServiceRank] END DESC,
        CASE WHEN (@DescSortDir = 1 AND @SortBy = ‚Public‘)THEN [PublicRank] END,
        CASE WHEN (@DescSortDir = 0 AND @SortBy = ‚Public‘)THEN [PublicRank] END DESC;
GO

Příklad takto napsané procedury předpokládá tabulku, ve které máme mimo jiné tři zvláštní sloupce (EditorRank, ServiceRank, PublicRank) určené k uživatelskému třídění nezávislém na ostatních sloupcích, což se hodí v případě, že obsah v tabulce sice lze třeba abecedně setřídit, avšak pro aplikaci toto automatické třídění není vhodné. Zvláštním sloupcem typu INT si tak může sám uživatel volit, jak budou údaje setříděné. Pokud je takových sloupců více, jako v našem příkladu, nebo chceme-li volit třídění i podle dalších sloupců, procedura se bude hodit.

Parametr @SortBy nastavujeme na některou z možností pro třídění (v našem příkladu některou z hodnot 'Editor', 'Service', 'Public'), parametr @DescSortDir nastavíme na 1 pokud chceme setřídění sestupné (DESC), jinak nastavujeme 0.

Závěrem si ještě ukážeme, jak můžeme získat obsah sloupce, podle kterého se právě třídí, i do výstupu klientovi (tedy mezi ostatními sloupci vyjmenovanými za klíčovým slovem SELECT):

CASE @SortBy
    WHEN (‚Editor‘) THEN [EditorRank]
    WHEN (‚Service‘) THEN [ServiceRank]
    WHEN (‚Public‘) THEN [PublicRank]
END AS [Rank]

Doporučuji vám stáhnout si ukázkový zdroj procedury a pro lepší pochopení si vše otestovat v praxi.