PAGINAR RESULTADOS

Hola Jorge, bueno, hay varias formas de "paginar" en SQL Server, yo soy nuevo
en estos foros pero tengo mi propio foro en www.iquos-bi.com, no se si el
codigo que te ponga aqui lo puedas ver completo, en caso de que no, estare
publicando un articulo proximamente con el detalle.

Si quieres paginar con SQL Server 7.0, este procedimiento almacenado
contiene un ejemplo de como hacerlo

CREATE PROCEDURE MyProcedure

@Page int,
@Size int

AS

DECLARE @Start int, @End int

BEGIN TRANSACTION GetDataSet

SET @Start = (((@Page - 1) * @Size) + 1)
IF @@ERROR <> 0
GOTO ErrorHandler

SET @End = (@Start + @Size - 1)
IF @@ERROR <> 0
GOTO ErrorHandler

CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @@ERROR <> 0
GOTO ErrorHandler

INSERT INTO #TemporaryTable
SELECT ...
// Any kind of select statement is possible with however many joins
// as long as the data selected can fit into the temporary table.
IF @@ERROR <> 0
GOTO ErrorHandler

SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)
IF @@ERROR <> 0
GOTO ErrorHandler

DROP TABLE #TemporaryTable

COMMIT TRANSACTION GetDataSet
RETURN 0

ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR


Con SQL Server 2000

Este te permite paginar, el ejemplo esta escrito con AdventureWorks

CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
@orderedOnStart datetime,
@orderedOnEnd datetime,
@pageNumber int,
@pageSize int,
@sortExpression varchar(100),
@sortOrder varchar(4),
@virtualCount int OUTPUT
AS

/*
Make sure that the page number is at least 1
*/
IF @pageNumber < 1
BEGIN
SET @pageNumber = 1
END

SELECT
@virtualCount = COUNT(*)
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd

DECLARE @lastKeyValue numeric(18,0)
DECLARE @lastAscendingSortValue SQL_Variant
DECLARE @lastDescendingSortValue SQL_Variant

DECLARE @numberToIgnore int

SET @numberToIgnore = (@pageNumber-1) * @pageSize

IF @numberToIgnore > 0
BEGIN
/*
Get the last available sort data and unique key
value from the last page.
*/
SET ROWCOUNT @numberToIgnore

SELECT
@lastKeyValue = [UniqueValue],
@lastAscendingSortValue = [AscendingSort],
@lastDescendingSortValue = [DescendingSort]
FROM
(
SELECT
Header.[SalesOrderID] AS [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [AscendingSort]
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
) AS Derived
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[UniqueValue] ASC
END

/*
Select the first @pageSize records that come after the last sort
data/unique value from the last page. If this is the first page,
just get the first @pageSize records.
*/

SET ROWCOUNT @pageSize

SELECT
[SalesOrderID],
[OrderDate],
[TotalDue],
[CustomerID]
FROM
(
SELECT
[SalesOrderID],
[OrderDate],
[TotalDue],
[CustomerID],
[SalesOrderID] As [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [AscendingSort]
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
) Derived
WHERE
(
@lastAscendingSortValue IS NULL
AND @lastDescendingSortValue IS NULL
AND @lastKeyValue IS NULL
)
OR
(
(@lastAscendingSortValue IS NOT NULL)
AND
(
([AscendingSort] > @lastAscendingSortValue)
OR
(
[AscendingSort] = @lastAscendingSortValue
AND [UniqueValue] > @lastKeyValue
)
)
)
OR
(
(@lastDescendingSortValue IS NOT NULL)
AND
(
([DescendingSort] < @lastDescendingSortValue)
OR
(
[DescendingSort] = @lastDescendingSortValue
AND [UniqueValue] > @lastKeyValue
)
)
)
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[SalesOrderID] ASC

SET ROWCOUNT 0
GO


Con SQL Server 2005

CREATE PROCEDURE [dbo].[sproc_get_clients]
@PageSize [int] = -1,
@CurrentPage [int] = -1
WITH EXECUTE AS CALLER
AS
SELECT
ROW_NUMBER() OVER(ORDER BY client_name ASC) AS rownum,
client_id,
client_name
INTO
#tmp_tbl_client
FROM
tbl_client

CREATE UNIQUE CLUSTERED INDEX
idx_uc_rownum
ON
#tmp_tbl_client(rownum)

SELECT
rownum,
client_id,
client_name,
FROM
#tmp_tbl_client
WHERE
rownum BETWEEN (@CurrentPage-1)*@PageSize+1 AND
@CurrentPage*@PageSize
ORDER BY
client_name ASC


Espero que te sea util...

recuerda visitarnos

John Jairo Bocachica
www.iquos-bi.com
Colombia

"Jorge Reyes" escribió:

Hola, un saludo a todos, tengo un pequeño problemita jeje, verán necesito que
cuando ejecute algun store procedure (que yo mismo diseñe) si el resultado me
devuelve mas de 41 filas entonces me permita obtenerlas por bloques de 41
registros, no se si pueda hacerlos incluyendo un campo mas en el set de
resultados el cual contenga el numero de bloque (o pagina) a devolverme o si
sea necesario crear mas de 1 store procedure para primero saber cuantas
paginas tendrá mi consulta, en fin, acudo a ustedes para que me den su
opinion sobre cual sería la manera mas practica de hacer esto, pensando en
que no solo lo voy a ocupar para un store procedure en particular, sino para
todos aquellos que me devuelvan mas de 41 filas. Muchas Gracias Salu...2!!!!

Hola John

Ya visite tu pagina e incluso me suscribi, espero visitarla regularmente.

Javir Loria, recomienda para paginacion:
http://www.configuracionesintegrale...articulo%5
Saludos
IIslas


"John Bocachica" wrote:

Hola Jorge, bueno, hay varias formas de "paginar" en SQL Server, yo soy nuevo
en estos foros pero tengo mi propio foro en www.iquos-bi.com, no se si el
codigo que te ponga aqui lo puedas ver completo, en caso de que no, estare
publicando un articulo proximamente con el detalle.

Si quieres paginar con SQL Server 7.0, este procedimiento almacenado
contiene un ejemplo de como hacerlo

CREATE PROCEDURE MyProcedure

@Page int,
@Size int

AS

DECLARE @Start int, @End int

BEGIN TRANSACTION GetDataSet

SET @Start = (((@Page - 1) * @Size) + 1)
IF @@ERROR <> 0
GOTO ErrorHandler

SET @End = (@Start + @Size - 1)
IF @@ERROR <> 0
GOTO ErrorHandler

CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @@ERROR <> 0
GOTO ErrorHandler

INSERT INTO #TemporaryTable
SELECT ...
// Any kind of select statement is possible with however many joins
// as long as the data selected can fit into the temporary table.
IF @@ERROR <> 0
GOTO ErrorHandler

SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)
IF @@ERROR <> 0
GOTO ErrorHandler

DROP TABLE #TemporaryTable

COMMIT TRANSACTION GetDataSet
RETURN 0

ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR


Con SQL Server 2000

Este te permite paginar, el ejemplo esta escrito con AdventureWorks

CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
@orderedOnStart datetime,
@orderedOnEnd datetime,
@pageNumber int,
@pageSize int,
@sortExpression varchar(100),
@sortOrder varchar(4),
@virtualCount int OUTPUT
AS

/*
Make sure that the page number is at least 1
*/
IF @pageNumber < 1
BEGIN
SET @pageNumber = 1
END

SELECT
@virtualCount = COUNT(*)
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd

DECLARE @lastKeyValue numeric(18,0)
DECLARE @lastAscendingSortValue SQL_Variant
DECLARE @lastDescendingSortValue SQL_Variant

DECLARE @numberToIgnore int

SET @numberToIgnore = (@pageNumber-1) * @pageSize

IF @numberToIgnore > 0
BEGIN
/*
Get the last available sort data and unique key
value from the last page.
*/
SET ROWCOUNT @numberToIgnore

SELECT
@lastKeyValue = [UniqueValue],
@lastAscendingSortValue = [AscendingSort],
@lastDescendingSortValue = [DescendingSort]
FROM
(
SELECT
Header.[SalesOrderID] AS [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [AscendingSort]
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
) AS Derived
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[UniqueValue] ASC
END

/*
Select the first @pageSize records that come after the last sort
data/unique value from the last page. If this is the first page,
just get the first @pageSize records.
*/

SET ROWCOUNT @pageSize

SELECT
[SalesOrderID],
[OrderDate],
[TotalDue],
[CustomerID]
FROM
(
SELECT
[SalesOrderID],
[OrderDate],
[TotalDue],
[CustomerID],
[SalesOrderID] As [UniqueValue],
CASE
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'DESC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [DescendingSort],
CASE
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'CUSTOMERID'
THEN
CONVERT(SQL_Variant, [CustomerID])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'TOTALDUE'
THEN
CONVERT(SQL_Variant, [TotalDue])
WHEN
UPPER(@sortOrder) = 'ASC'
AND UPPER(@sortExpression) = 'ORDERDATE'
THEN
CONVERT(SQL_Variant, [OrderDate])
ELSE
NULL
END AS [AscendingSort]
FROM
Sales.SalesOrderHeader Header
WHERE
Header.[OrderDate] >= @orderedOnStart
AND Header.[OrderDate] < @orderedOnEnd
) Derived
WHERE
(
@lastAscendingSortValue IS NULL
AND @lastDescendingSortValue IS NULL
AND @lastKeyValue IS NULL
)
OR
(
(@lastAscendingSortValue IS NOT NULL)
AND
(
([AscendingSort] > @lastAscendingSortValue)
OR
(
[AscendingSort] = @lastAscendingSortValue
AND [UniqueValue] > @lastKeyValue
)
)
)
OR
(
(@lastDescendingSortValue IS NOT NULL)
AND
(
([DescendingSort] < @lastDescendingSortValue)
OR
(
[DescendingSort] = @lastDescendingSortValue
AND [UniqueValue] > @lastKeyValue
)
)
)
ORDER BY
[AscendingSort] ASC,
[DescendingSort] DESC,
[SalesOrderID] ASC

SET ROWCOUNT 0
GO


Con SQL Server 2005

CREATE PROCEDURE [dbo].[sproc_get_clients]
@PageSize [int] = -1,
@CurrentPage [int] = -1
WITH EXECUTE AS CALLER
AS
SELECT
ROW_NUMBER() OVER(ORDER BY client_name ASC) AS rownum,
client_id,
client_name
INTO
#tmp_tbl_client
FROM
tbl_client

CREATE UNIQUE CLUSTERED INDEX
idx_uc_rownum
ON
#tmp_tbl_client(rownum)

SELECT
rownum,
client_id,
client_name,
FROM
#tmp_tbl_client
WHERE
rownum BETWEEN (@CurrentPage-1)*@PageSize+1 AND

Además de lo que te han indicado,

Te recomiendo estos artículos de Itzik Ben-Gan (sólo los podrás leer al
completo si eres suscriptor, pero si nolo eres podrás leer sólo una parte de
ellos y descargar los fuentes de los ejemplos):
http://www.sqlmag.com/Article/Artic...aging.html
http://www.sqlmag.com/Article/Artic..._2005.html

Un saludo
Salvador Ramos
Murcia - España

[Microsoft MVP SQL Server]
www.helpdna.net (información sobre SQL Server y .NET)


"Jorge Reyes" escribió en el mensaje
news:

Hola, un saludo a todos, tengo un pequeño problemita jeje, verán necesito
que
cuando ejecute algun store procedure (que yo mismo diseñe) si el resultado
me
devuelve mas de 41 filas entonces me permita obtenerlas por bloques de 41
registros, no se si pueda hacerlos incluyendo un campo mas en el set de
resultados el cual contenga el numero de bloque (o pagina) a devolverme o
si
sea necesario crear mas de 1 store procedure para primero saber cuantas
paginas tendrá mi consulta, en fin, acudo a ustedes para que me den su
opinion sobre cual sería la manera mas practica de hacer esto, pensando en
que no solo lo voy a ocupar para un store procedure en particular, sino
para
todos aquellos que me devuelvan mas de 41 filas. Muchas Gracias
Salu...2!!!!