Univerzální stránkovací procedura v MS SQL

MS SQL nepodporuje triviální možnost stránkování, jako například MySQL, pomocí klauzule LIMIT. O stránkování v MS SQL jsme psali již dříve, tentokrát si ale ukážeme univerzální proceduru, pomocí které je možné stránkovat údaje z libovolné tabulky nebo pohledu.

Ačkoli Microsoft SQL Server podporuje výraz TOP, jeho použití pro stránkování záznamů není úplně jednoduché. Hlavním problémem je, že při stránkování typicky potřebujeme záznamy „od-do“, výraz TOP nám však může poskytnout pouze záznamy „od“. Druhým problémem je, že hodnotu TOP nelze parametrizovat a není tedy možné tuto hodnotu předávat jako parametr do funkce nebo uložené procedury. Pro stránkování se proto často využívají metody, které načtou najednou všechny záznamy do paměti a stránkování pak probíhá až na straně aplikace (například použití objektu DataSet stránkování v ovládacím prvku DataGrid) nebo metody, které pro získání údajů dané stránky využívají dočasnou tabulku (viz článek Stránkování v ovládacím prvkuDataGrid prvku DataGrid inteligentněji).

V tomto článku vytvoříme univerzální proceduru, která bude použitelná pro stránkování záznamů z libovolné tabulky či pohledu a navíc vrátí všechny potřebné údaje pro stránkování. Ačkoli je možné tuto proceduru použít pro libovolnou aplikaci, zvlášť jsem se zaměřil na použití pro stránkování v DataGridu, z čehož se odvíjí i pojmenování vlastností a hodnot, se kterými budeme pracovat. Nejprve si shrňme důležité předpoklady pro stránkování záznamů:

1. Typicky potřebujeme celkový počet záznamů, nejen pro zobrazení, ale také aby bylo možné rozkreslit ovládací prvky pro stránkování – posun vpřed či zpět, případně zobrazit číselné odkazy pro přechod na konkrétní stránky, prvek DataGrid tuto hodnotu nazývá VirtualItemCount.
2. Velikost (délka) stránky nám určí počet záznamů, ze kterých se skládá jedna stránka, tedy počet záznamů (řádků) na stránce – pro DataGrid je pojmenovaná PageSize.
3. Z celkového počtu záznamů a počtu záznamů na stránce vypočteme celkový počet stránek. DataGrid si tuto hodnotu stanovuje vnitřně sám, přesto však může být užitečná při použití jiné metody zobrazení a já jsem ji pojmenoval PageCount.
4. Potřebujeme také znát index aktuální stránky, jednak abychom dostali data pro danou stránku a jednak pro vykreslení prvků uživatelského rozhraní – shodně s prvkem DataGrid jsem tuto hodnotu pojmenoval CurrentPageIndex.
5. Data je nutné pro potřeby stránkování seřadit podle nějakého klíče, ať se nám to líbí nebo ne – data, která nejsou setříděná, nelze korektně stránkovat. Prvek DataGrid má podobnou vlastnost, proto ji nazveme KeyField.
6. Někdy je potřeba přidat omezující podmínku (WHERE …), proto naše procedura bude mít i parametr pro možnost zadání takovéto podmínky – názvem jsem se inspiroval tentokrát u objektu DataView, pojmenoval jsem ho RowFilter.

Naše univerzální procedura si poradí s problémem parametrizace hodnoty pro výraz TOP (viz dále), proto pro naše stránkování využijeme triku se dvěma vnořenými dotazy – princip si ukážeme na příkladu.

Celkový počet záznamů VirtualItemCount zjistíme triviálně pomocí COUNT:

COUNT (*) FROM table

Aby byla naše procedura ještě užitečnější, přidáme i výpočet celkového počtu stran PageSize – mimo jiné se nám bude hodit pro rozhodnutí, zda není požadována neexistující stránka. Počet stránek získáme jako nejbližší vyšší číslo (funkce ceiling) podílu celkového počtu položek a délky stránky:

Pocet_stran = Ceiling(Pocet_zaznamu/Delka_stranky)

Pro přepis do SQL dotazu si musíme pomoci fintou, která donutí MS SQL zpracovat výraz s přesností alespoň na jedno desetinné číslo:

CEILING(@ItemCount*1.0/@PageSize)

Až na RowFilter máme nyní jasno ve všech vstupních hodnotách a v hodnotách, které bude procedura vracet. Zbývá proto popsat způsob, který použijeme pro stránkování – popíšeme si ho opět na příkladu.

Příklad použití

V tabulce máme celkem 675 záznamů, požadujeme data pro třetí stránku, velikost stránky (počet řádků na stránce) má být 15. Záznamy budeme pro jednoduchost řadit podle sloupce ID, který zároveň přesně odpovídá pořadovému číslu záznamu. Vycházíme z toho, že první stránka obsahuje nejnovější záznamy, tedy od záznamu 661 po záznam 675.

V prvním kroku získáme všechny záznamy setříděné sestupně podle sloupce ID:

SELECT * FROM table ORDER BY id DESC

Ve druhém kroku nás již čeká určitá aritmetika – pomocí TOP ze všech záznamů odřízneme záznamy předchozích stránek. Musíme tedy vypočítat rozdíl všech záznamů a záznamů předcházejících – nazvěme tento rozdíl Odrolovat – tedy o kolik záznamů odrolovat vpřed. V našem případě to bude 645. Ukažme si, jak tento výsledek získáme:

Odrolovat = Záznamy_celkem – (Velikost_stránky * Index_stránky)

Pokud do vztahu dosadíme hodnoty našeho příkladu, bude vypadat takto (podotýkám, že třetí stránka má index 2 – počítáme od 0):

675 – (15 * 2) = 645

Ze setříděného seznamu tedy musíme pro třetí stránku odříznout posledních 645 záznamů – výsledkem budou záznamy s ID 645 až 1:

SELECT TOP 645 * FROM table ORDER BY id DESC

Ve třetím kroku nastavíme maximum získaných řádků pomocí SET ROWCOUNT na počet řádku na stránce – získáme tak jen potřebné řádky, v našem případě jich bude 15, tedy záznamy 645-631:

SET ROWCOUNT 15
SELECT TOP 645 * FROM table ORDER BY id DESC

Nakonec už jen otočíme pořadí setřídění záznamů – dotaz vnoříme do dotazu pro setřídění v opačném pořadí, výsledkem budou záznamy s ID 631 – 645, tedy přesně ty, které odpovídají třetí stránce:

SET ROWCOUNT 15
SELECT * FROM (SELECT TOP 645 * FROM table ORDER BY id DESC) ORDER BY id

Namísto SET ROWCOUNT bychom mohli bychom použít i výraz TOP – výsledek bude stejný, jen zpracování bude asi pomalejší, při použití SET ROWCOUNT se totiž už vnitřní dotaz ukončí po vrácení 15 záznamů a tak vnější dotaz, který obrací pořadí pro potřebu zobrazení, pracuje jen s 15 záznamy:

SELECT TOP 15 * FROM (SELECT TOP 645 * FROM table ORDER BY id DESC) ORDER BY id

Teoreticky bychom při použití SET ROWCOUNT vystačili pouze s jedním dotazem, prakticky by nás to ovšem nutilo zobrazovat data pouze v onom sestupném pořadí, a to není vždy vhodné, proto jsem do naší univerzální procedury zahrnul dva vnořené dotazy pro úpravu pořadí záznamů.

Abychom obešli chybějící parametrizaci hodnoty pro TOP, využijeme možnosti spouštět dotaz sestavený v řetězci pomocí EXEC. Jak uvidíme dále, bude se nám hodit i systémová procedura sp_ExecuteSql. Naše procedura bude vracet dvě sady záznamů, první sada vrátí jeden řádek obsahující dříve popsané vlastnosti potřebné pro stránkování, druhá sada vrací záznamy (řádky) požadované stránky. Jenom poznamenám, že v systému .NET lze s více sadami vrácenými z dotazu pracovat pohodlně jako s takzvanými resultsety, viz článek Grafy v ASP.NET.

Sestavení procedury

Nyní nám už zbývá jen popsaný postup sestavit do kódu uložené procedury – to jsem provedl za vás a proceduru jsem nazval PaginateTable. Z níže uvedeného popisu kódu pak dále vyplyne i zpracování výše popsané podmínky RowFilter.

CREATE PROCEDURE [dbo].[PaginateTable]
(
  — deklarace vstupních parametrů a nastavení výchozích hodnot
  @TableName VARCHAR(64),
  @PageSize INT = 30,
  @CurrentPageIndex INT = 0,
  @KeyField VARCHAR(32) = ‚Id‘,
  @RowFilter VARCHAR(3128) = NULL
)
AS
  — pomocná proměnná pro celkový počet záznamů
  DECLARE @ItemCount INT
  — pomocná proměnná pro počet všech stránek
  DECLARE @PageCount INT
  — pomocná proměnná pro SQL dotaz určující počet všech záznamů
  DECLARE @ItemCountSQL NVARCHAR(1024)
  — pomocná proměnná pro podmínku v SQL dotazu při použití podmínky RowFilter
  DECLARE @WhereSQL VARCHAR(3128)
  — pokud je zadána vlastnost RowFilter, pak ji převezmeme do proměnné @WhereSQL
  IF (@RowFilter IS NOT NULL AND @RowFilter <> “)
    SET @WhereSQL = ‚ WHERE ‚ + @RowFilter
  ELSE
    SET @WhereSQL = ‚ ‚
  — složit SQL dotaz pro výpočet celkového počtu záznamů, přidáváme jméno tabulky a případně omezující podmínku @WhereSQL
  SET @ItemCountSQL = ‚SELECT @ItemCount = COUNT(*) FROM ‚ + @TableName + @WhereSQL
  — spustit připravený dotaz pomocí sp_ExecuteSql, získáme tak celkový počet záznamů do proměnné @ItemCount
  EXEC sp_ExecuteSql @ItemCountSQL, N’@ItemCount INT OUT‘, @ItemCount OUT
  — stanovit počet stránek potřebných pro zobrazení všech záznamů
  SET @PageCount = CEILING(@ItemCount*1.0/@PageSize)
  — připravit první sadu výsledku s údaji VirtualItemCount, CurrentPageIndex (pokud není požadována neexistující stránka, jinak NULL), PageSize, PageCount
  SELECT
    @ItemCount AS VirtualtemCount,
    CurrentPageIndex =
      CASE
        WHEN @CurrentPageIndex < @PageCount THEN @CurrentPageIndex
        ELSE NULL
      END,
    @PageSize AS PageSize, @PageCount AS PageCount
  — pomocná proměnná pro odrolování „za předchozí záznamy“
  DECLARE @ItemRollOut INT
  — předávat výsledek pouze, pokud je délka stránky větší než 0, jinak vrať NULL
  IF (@PageSize > 0)
    BEGIN
      — připravit počet odrolovávaných záznamů jako rozdíl všech a aktuální stránky vynásobené délkou stránky
      SET @ItemRollOut = @ItemCount – @PageSize * @CurrentPageIndex
      — pokud již není co odrolovávat, vrať NULL
      IF (@ItemRollOut > 0)
        BEGIN
          — nastavit omezení počtu vrácených řádků na počet řádků na stránce
          SET ROWCOUNT @PageSize
          — pomocná proměnná pro SQL dotaz vracející odpovídající záznamy stránky
          DECLARE @ItemSQL VARCHAR(6144)
          — sestavit vnořené dotazy s použitím názvu tabulky @TableName, sloupce pro třídění @KeyField a případné omezující podmínky uložené ve @WhereSQL
          SET @ItemSQL = ‚SELECT * FROM (SELECT TOP ‚ + CAST(@ItemRollOut AS VARCHAR(10)) + ‚ * FROM ‚ + @TableName + @WhereSQL + ‚ORDER BY ‚ + @KeyField + ‚ DESC) AS TMP1 ORDER BY ‚ + @KeyField + ‚ ASC‘
          — spustit dotaz, řádky tabulky pro danou stránku tak budou vráceny jako druhá sada
          EXEC (@ItemSQL)
        END
      ELSE
        SELECT NULL
    END
    ELSE
      SELECT NULL
GO

Kód je řádně okomentován, přesto bych rád zvlášť upozornil na použití procedury sp_ExecuteSql – v naší proceduře potřebujeme dotaz poskládat uvnitř a současně potřebujeme dostat výsledek takto spuštěného dotazu v proměnné, s jejíž hodnotou můžeme uvnitř procedury pracovat. To není úplně triviální, použil jsem proto tip ze serveru Umachandar, kde mimo jiné ukazují, jak převzít výstupní hodnotu z dynamického SQL dotazu. V proceduře používáme také funkci CAST – je důležité při skládání řetězce dotazu převést hodnotu typu INT (počet řádků pro odrolování) na typ VARCHAR, aby složení řetězce bylo korektní.

Pokud není pro aplikaci vyhovující vracení dvou sad, je možné proceduru upravit tak, aby požadované hodnoty z první sady vracela raději pomocí výstupních parametrů.

Je zřejmé, že procedura se nehodí pro komplikované dotazy sestavované z mnoha propojených tabulek, v tom případě je dobré si předem raději připravit zvláštní pohled (View). Uplatnění určitě najde v různých administračních rozhraních, zvláště v intranetu, kde je často mnoho jednoduchých tabulek s relativně malým počtem záznamů. Určitým nedostatkem procedury je fakt, že pokud provádíme dynamický dotaz pomocí EXEC, pak musí mít uživatel spouštějící proceduru nejen právo spustit danou proceduru, ale také musí mít právo SELECT nad používanými tabulkami.

Na závěr bych shrnul několik užitečných pravidel pro využívání uložených procedur. Při volání uložené procedury by měl být vždy uveden její vlastník (obvykle dbo), ten by ostatně měl být uváděn vždy před každým názvem objektu – zabrání se tak zjišťování názvu (name resolution) a odpadne také nutnost zjišťování práv uživatele na jednotlivé objekty. Pro ještě univerzálnější použití by se hodilo umístit proceduru do databáze master, pokud ji dáme do názvu prefix sp_, bude pak tato procedura dostupná při práci v kterékoli databázi prostým voláním dbo.sp_PaginateTable.

K dispozici je vám samozřejmě zdrojový kód.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.