Paging mit SQLGlobalCursor

Problembeschreibung

Große Datenmengen sollen seitenweise (Paging) angezeigt werden. Viele Controls benötigen als DataSource jedoch die gesamte Datenmenge, obwohl der Benutzer nur einzelne Seiten ansieht. Wichtig ist aber, die Gesamtanzahl zu kennen. Für den MS SQL Server gibt es dafür eine Lösung mit einem globalen Cursor.

Lösung

Beispiel: Tabelle „Kunde“ mit 1.200.000 Datensätzen.

Tabellenstruktur „Kunde“

Auswahl der im Herbst Geborenen:

SELECT *
FROM [dbo].[Kunde]
WHERE DATEPART(m, Geburtsdatum) BETWEEN 9 AND 11
ORDER BY Geburtsdatum, Name, Vorname;
-- (87790 Zeilen betroffen) -> bei 50/Seite: 1756 Seiten

Ein globaler SQL-Cursor, verpackt in eine Stored Procedure, holt die Daten portionsweise und liefert zugleich die Gesamtanzahl:

CREATE PROCEDURE [dbo].[usp_GetResultPaging]
(
    @intAbsPos          Int = 1,             -- ab welcher Position gelesen wird
    @chrFromTable       VARCHAR(Max) = '',   -- aus Tabelle/Sicht
    @chrWHERE           VARCHAR(Max) = '',   -- Where-Bedingung
    @countPerPage       int = 30,            -- Anzahl je Seite
    @chrPKColumnName    VARCHAR(150),        -- Primary Key
    @chrOrderBY         VARCHAR(500),        -- Sortierung
    @chrSELECT          VARCHAR(Max),        -- Auswahl
    @chrCursorName      VARCHAR(200),        -- Cursor-Name
    @chrCursorOperation varChar(10) = 'READ' -- OPEN | READ | CLOSE
)
AS
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @i Int, @chrBuffer VARCHAR(100), @chrKeys VARCHAR(8000)
SET @chrKeys = ''; SET @chrBuffer = ''

IF @chrCursorOperation = 'OPEN'
BEGIN
    EXECUTE('DECLARE '+ @chrCursorName +' CURSOR GLOBAL SCROLL READ_ONLY KEYSET FOR SELECT '
            + @chrPKColumnName + ' FROM ' + @chrFromTable + @chrWHERE + @chrOrderBY)
    EXECUTE('OPEN '+ @chrCursorName)
END

IF @chrCursorOperation = 'OPEN' OR @chrCursorOperation = 'READ'
BEGIN
    DECLARE @sql nvarchar(100), @sqlNext nvarchar(100)
    SET @sql     = N'FETCH ABSOLUTE @intAbsPos FROM '+ @chrCursorName +' INTO @chrBuffer'
    SET @sqlNext = N'FETCH NEXT FROM '+ @chrCursorName +' INTO @chrBuffer'
    EXEC sp_executesql @sql, N'@intAbsPos int, @chrBuffer VARCHAR(100) OUTPUT', @intAbsPos, @chrBuffer OUTPUT
    SET @i = 1
    SET @chrKeys = '''' + @chrBuffer + ''','
    WHILE (@@FETCH_STATUS <> -1) AND (@i < @countPerPage)
    BEGIN
        EXEC sp_executesql @sqlNext, N'@chrBuffer VARCHAR(100) OUTPUT', @chrBuffer OUTPUT
        IF @@FETCH_STATUS <> -1 BEGIN
            SET @chrKeys = @chrKeys + '''' + @chrBuffer + ''','
            SELECT @i = @i + 1
        END
    END
    SET @chrKeys = '(' + Left(@chrKeys, Len(@chrKeys) - 1) + ')'
    EXECUTE(@chrSELECT + ', CONVERT(VARCHAR(20), ' + @@CURSOR_ROWS + ') as Anzahl FROM '
            + @chrFromTable + ' WHERE ' + @chrPKColumnName + ' IN ' + @chrKeys + @chrOrderBY)
END

IF @chrCursorOperation = 'CLOSE'
    EXECUTE('CLOSE '+ @chrCursorName + ' DEALLOCATE '+ @chrCursorName)

SET NOCOUNT OFF
RETURN

Aufruf (zweite Seite, 50 pro Seite):

EXEC [dbo].[usp_GetResultPaging]
    @intAbsPos = 51, @chrFromTable = N'dbo.[Kunde]',
    @chrWHERE = N' WHERE DATEPART(m, Geburtsdatum) BETWEEN 9 AND 11 ',
    @countPerPage = 50, @chrPKColumnName = N'Id',
    @chrOrderBY = N' ORDER BY Name, Vorname ', @chrSELECT = N'Select *',
    @chrCursorName = N'PagingCursor', @chrCursorOperation = N'OPEN'

Da der Cursor als GLOBAL angelegt ist, bleibt er in der offenen Verbindung erhalten. Ab dem zweiten Aufruf READ übergeben, zum Schluss mit CLOSE freigeben.

HinweisModerne SQL-Server-Versionen unterstützen serverseitiges Paging direkt per OFFSET … FETCH NEXT … ROWS ONLY, was in vielen Fällen einfacher ist als ein globaler Cursor.

Unterstützung von m.a.x. it

Bei plattformübergreifender App- und Software­entwicklung unterstützt Sie m.a.x. it mit individueller Softwareentwicklung.

Über m.a.x. it Die m.a.x. Informationstechnologie AG ist seit über 30 Jahren IT-Partner mittelständischer und großer Unternehmen in München und bietet maßgeschneiderte Lösungen und Services in den Bereichen Cloud, Cybersecurity, Netzwerk, Windows, Linux und Softwareentwicklung. Sie haben eine Frage zu diesem Artikel oder brauchen Unterstützung? Kontakt aufnehmen