SQL Server, Crear un procedimiento almacenado de paginación

Algunas veces en un formulario se muestran sólo algunos datos de todos los que tenemos que mostar y necesitamos algo para mostar todos, para ello utilizamos los botones típicos de paginación.

que nos permiten cargar el formulario con una vista completa y cambiar esta vista por la siguiente como si de página se tratara cada vez que pulsemos el botón correspondiente.

Presento a continuación un procedimiento almacenado de SQL Server para realizar una paginación.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[sp_glo_paginar] (@sintNPag SMALLINT, @sintPagActual SMALLINT, @varSQL VARCHAR(8000))

AS

      DECLARE @varTmpTable VARCHAR(20)-- Nombre de la tabla temporal

      DECLARE @varTmpSQLDespuesFROM VARCHAR(8000)-- Después del FROM

      DECLARE @varTmpSQLCampos VARCHAR(8000)   -- Campos seleccionados

      DECLARE @varTmpSQL VARCHAR(8000)   -- SQL final

      DECLARE @sintLenSQL SMALLINT -- Longitud de la cadena recibida

      DECLARE @sintPosicionSELECT SMALLINT     -- Posición del SELECT

      DECLARE @sintPosicionFROM SMALLINT       -- Posición del FROM

      DECLARE @sintPosicionPrimerAsterisco SMALLINT-- Posición de * para validar

      DECLARE @sintPosicionDISTINCT SMALLINT   -- Posición del distinct

      DECLARE @sintPosicionALL SMALLINT        -- Posición del ALL

      DECLARE @intPrincipioPagina INTEGER     -- Número de registro del que partimos

     

      SET @varSQL = UPPER(@varSQL)

      IF LEN(LTRIM(RTRIM(@varSQL))) = 0 OR @varSQL IS NULL

            BEGIN

                  PRINT 'Se debe indicar una sentencia SQL'

                  RETURN

            END

      SET @sintPosicionPrimerAsterisco = CHARINDEX('*', @varSQL)

      SET @sintLenSQL = LEN(@varSQL)

      SET @sintPosicionFROM = @sintLenSQL - CHARINDEX(' MORF ', REVERSE(@varSQL)) - 3

      IF @sintPosicionPrimerAsterisco < @sintPosicionFROM AND @sintPosicionPrimerAsterisco <> 0

            BEGIN

                  PRINT 'Se deben indicar los campos, no vale *'

                  RETURN

            END

      IF CHARINDEX(' TOP ', @varSQL) <> 0

            BEGIN

                  PRINT 'No tiene sentido utilizar TOP en este proceso'

                  RETURN

            END

      IF @sintNPag <= 0

            SET @sintNPag = 1

      IF @sintPagActual <= 0

            SET @sintPagActual = 1

      SET @intPrincipioPagina = @sintNPag * (@sintPagActual-1)

      SET @varTmpSQLCampos = LEFT(@varSQL, @sintPosicionFROM-1)

      SET @varTmpSQLDespuesFROM = SUBSTRING(@varSQL, @sintPosicionFROM, @sintLenSQL - @sintPosicionFROM + 1)

      SET @sintPosicionSELECT = CHARINDEX('SELECT ', @varTmpSQLCampos)

      SET @varTmpSQLCampos = STUFF(@varTmpSQLCampos, @sintPosicionSELECT, 6,'')

      SET @sintPosicionDISTINCT = CHARINDEX(' DISTINCT ', @varTmpSQLCampos)

      IF @sintPosicionDISTINCT <> 0

            SET @varTmpSQLCampos = STUFF(@varTmpSQLCampos, @sintPosicionDISTINCT + 1, 8,'')

      SET @sintPosicionALL = CHARINDEX(' ALL ', @varTmpSQLCampos)

      IF @sintPosicionALL <> 0

            SET @varTmpSQLCampos = STUFF(@varTmpSQLCampos, @sintPosicionALL + 1, 3,'')

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

      EXEC DBO.sp_glo_tmp_tabla_global @varTmpTable OUTPUT

      SET @varTmpSQL = ' SELECT TOP 0 ' + @varTmpSQLCampos +  ', IDENTITY(int, 1 ,1) intPosicionRegistro INTO ' + @varTmpTable + ' ' + @varTmpSQLDespuesFROM + CHAR(13)

      EXECUTE (@varTmpSQL)

      SET @varTmpSQLCampos = ''

      SELECT @varTmpSQLCampos = @varTmpSQLCampos + CASE @varTmpSQLCampos WHEN '' THEN '' ELSE ', ' END + name FROM tempdb..syscolumns WHERE id = OBJECT_ID('tempdb..' + @varTmpTable) AND name <> 'intPosicionRegistro' ORDER BY colorder

     

      SET @varTmpSQL = ' INSERT INTO ' + @varTmpTable + '(' + @varTmpSQLCampos + ')' + @varSQL + CHAR(13)

      SET @varTmpSQL = @varTmpSQL + ' SELECT TOP ' + CAST(@sintNPag AS VARCHAR(20)) + ' ' + @varTmpSQLCampos + ' , (SELECT max(intPosicionRegistro) FROM ' + @varTmpTable + ') intTotalRegistros  FROM ' + @varTmpTable + ' WHERE intPosicionRegistro > ' + CAST(@intPrincipioPagina AS VARCHAR(20)) + ' ORDER BY intPosicionRegistro ASC ' + CHAR(13)

      SET @varTmpSQL = @varTmpSQL + ' DROP TABLE ' + @varTmpTable

      EXECUTE (@varTmpSQL)

Una vez creado nuestro procedimiento almacenado en SQL Server sólo tendremos que añadir una llamada desde nuestro código para utilizarlo.

El formulario en .NET sería del tipo:

A continuación pongo un esbozo de cómo sería más o menos la estructura del código en .NET (ojo este código no funciona directamente es un código aproximativo)

Las funciones a definir por el desarrollador están entre corchetes y en verde [ ]

Imports System.Data.SqlClient

Imports Microsoft.SqlServer

Public Class Form1

    Private mblnEjecutaSP As Boolean

    Private mstrSql As String

    Private mintRegistrosPorPagina As Integer

    Private mintPaginaActual As Integer

    Private mintTotalPaginas As Integer

   

    'Procedimiento Paginar.

    'Desde el Código se llama:

    Public Sub Paginar(ByVal intNumeroRegistrosPagina As Integer, ByVal intNumeroPagina As Integer, ByVal strSql As String, ByVal blnEjecutaSP As Boolean)

        '[Definicion de conexión]

        rdoQ.SQL = "{ call dbo.sp_glo_paginar(" & intNumeroRegistrosPagina & "," & intNumeroPagina & "," & strSql & ") }"

        If Not [Fin de Fichero] Then

            mintPaginaActual = intNumeroPagina

            cmdIrPrincipio.Enabled = (mintPaginaActual <> 1)

            cmdIrAtras.Enabled = (mintPaginaActual <> 1)

            If mintTotalPaginas = 0 Then

                mblnEjecutaSP = blnEjecutaSP

                cmdIrPrincipio.Visible = True

                cmdIrAtras.Visible = True

                cmdIrAdelante.Visible = True

                cmdIrFinal.Visible = True

                mintRegistrosPorPagina = intNumeroRegistrosPagina

                mstrSql = strSql

            End If

            lblPagina.Text = "Pág " & mintPaginaActual & "/" & mintTotalPaginas

            cmdIrAdelante.Enabled = (mintPaginaActual <> mintTotalPaginas)

            cmdIrFinal.Enabled = (mintPaginaActual <> mintTotalPaginas)

        Else

            cmdIrAdelante.Enabled = False

            cmdIrFinal.Enabled = False

            cmdIrPrincipio.Enabled = False

            cmdIrAtras.Enabled = False

        End If

    End Sub

    Private Sub cmdIrAdelante_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrAdelante.Click

        Paginar(mintRegistrosPorPagina, mintPaginaActual + 1, mstrSql, mblnEjecutaSP)

        '[Llamada a nuestro procedimiento de Carga ]

    End Sub

    Private Sub cmdIrPrincipio_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrPrincipio.Click

        Paginar(mintRegistrosPorPagina, 1, mstrSql, mblnEjecutaSP)

        '[Llamada a nuestro procedimiento de Carga ]

    End Sub

    Private Sub cmdIrAtras_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrAtras.Click

        Paginar(mintRegistrosPorPagina, mintPaginaActual - 1, mstrSql, mblnEjecutaSP)

        '[Llamada a nuestro procedimiento de Carga ]

    End Sub

    Private Sub cmdIrFinal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdIrFinal.Click

        Paginar(mintRegistrosPorPagina, mintTotalPaginas, mstrSql, mblnEjecutaSP)

        '[Llamada a nuestro procedimiento de Carga ]

    End Sub

End Class

Y aquí un esbozo en Visual Basic 6.0

Global dbDocumentos As rdoConnection

Public Sub Paginar(intNumeroRegistrosPagina As Integer, intNumeroPagina As Integer, strSql As String, blnEjecutaSP As Boolean)
Dim rdoQ As New rdoQuery
Dim i As Integer
Dim mintPaginaActual As Integer
Dim mintTotalPaginas As Integer
Dim mblnEjecutaSP As Boolean
Dim mintRegistrosPorPagina As Integer
Dim mstrSql As String
Dim rec As rdoResultset

rdoQ.ActiveConnection = dbDocumentos
rdoQ.sql = "{ call dbo.sp_glo_paginar(?,?,?) }"

rdoQ.rdoParameters(0) = intNumeroRegistrosPagina

rdoQ.rdoParameters(1) = intNumeroPagina

rdoQ.rdoParameters(2) = strSql

rec = rdoQ.OpenResultset
If Not rec.EOF Then


mintPaginaActual = intNumeroPagina

cmdIrPrincipio.Enabled = (mintPaginaActual <> 1)

cmdIrAtras.Enabled = (mintPaginaActual <> 1)
If mintTotalPaginas = 0 Then


mblnEjecutaSP = blnEjecutaSP
cmdIrPrincipio.Visible = True
cmdIrAtras.Visible = True
cmdIrAdelante.Visible = True
cmdIrFinal.Visible = True
cboIrPagina.Visible = True


cboIrPagina.Clear()

mintRegistrosPorPagina = intNumeroRegistrosPagina
mintTotalPaginas = Fix(rec!IntTotalRegistros / mintRegistrosPorPagina) + IIf(rec!IntTotalRegistros Mod mintRegistrosPorPagina <> 0, 1, 0)

mstrSql = strSql
For i = 1 To mintTotalPaginas


cboIrPagina.AddItem i
Next i

cboIrPagina.Enabled = (mintTotalPaginas > 1)
End If
lblPagina.Caption = "Pág " & mintPaginaActual & "/" & mintTotalPaginas

cmdIrAdelante.Enabled = (mintPaginaActual <> mintTotalPaginas)

cmdIrFinal.Enabled = (mintPaginaActual <> mintTotalPaginas)
Else
cmdIrAdelante.Enabled = False
cmdIrFinal.Enabled = False
cmdIrPrincipio.Enabled = False
cmdIrAtras.Enabled = False
End If

End Sub