Purpose
Provide an efficient method for sorting and paging large datasets on a Microsoft SQL Server 2000 database that does not require the use of concatenated SQL. Primarily, this is a proof-of-concept piece and you will need to do your own performance testing to see if it meets your goals.
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 < pagenumber =" 1" virtualcount =" COUNT(*)">= @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
