Custom Sorting and Paging in SQL Server Stored Procedure
This post is about achieving pagination using stored procedure, without using dynamic SQL. The solution presented should work for both SQL Server 2000 and 2005.
Most of the web application displaying transactional data will have a data grid with sorting and paging functionality and some filter criteria.
We usually use the default sorting/paging functionality available in the datagrid. But, this requires the entire search resultset to be cached in the client side, which is fine for a few hundered records but can be a drag as the number of records increase.
I was pulled into a situation where there were millions of records in the table and the filter criteria was not good enough to bring down the number of records being sent to the client side.
To fix this issue, we had to implement the sorting and paging in the database and send only the required data to be viewed in the page.
A quite common solution is to use dynamic SQL. I however wanted to come up with a solution without using dynamic SQL. So here it goes.
These are my requirements:
1) In AdventureWorks database I need to write a stored procedure to select data from Sales.SalesOrderDetail
2) The table should be joined with the Production.Product table to get the product name for the product code
3) The SP should allow sorting (ASC or DESC) on the following columns
- ORDER_ID
- TRACKING_NBR
- PRODUCT_NAME
- MODIFIED_DATE
5)The filtering can be done on columns available for sorting
6) The SP will accept the page number and page size as input and return only that page based on the sort and filter codition
7) The SP will also have to return the total records that are fetched based on the filter condition without pagination. This will be returned as an output parameter.
The above requirements should encompass all the functionality expected out of a database side pagination.
Below is the solution without using dynamic SQL.
Please Note: I am not the guy who came out with all the techniques used to build the solution SP. Many of the techniques had been commonly used even before I knew what SQL Server was. I am just collating all the ideas in one place.
Please feel free to give your comments on how to improve this. If you think I missed a common functionality required in the SP, let me know, I will be happy to add it.
CREATE PROC SalesOrderDetailCustomSort
(
@in_SalesOrderID INT = NULL, /* FILTER - ORDER ID */
@in_CarrierTrackingNumber VARCHAR(25) = '', /* FILTER - LIKE SEARCH ON TRACKING NUMBER */
@in_ProductName NVARCHAR(50) = '', /* FILTER - PRODUCT NAME */
@in_ModifiedDateFrom DATETIME = '1753-01-01', /* FILTER - MODIFIED DATE FROM */
@in_ModifiedDateTo DATETIME = '9999-12-31', /* FILTER - MODIFIED DATE TO */
@PageSize SMALLINT = 25, /* NUMBER OF RECORDS PER PAGE */
@TargetPage SMALLINT = 1, /* PAGE THAT NEEDS TO BE RETURNED */
@OrderBy VARCHAR(50) = '', /* ORDER BY COLUMN. WILL HAVE THE SAME NAME
AS THE RESULT SET COLUMN. IN OUR EXAMPLE
WE ALLOW SORTING ON THE FOLLOWING COLUMNS
ORDER_ID
TRACKING_NBR
PRODUCT_NAME
MODIFIED_DATE
*/
@SortOrder VARCHAR(4) = '', /* SORT ORDER - ASC OR DESC */
@TotalRecCount INT OUTPUT /* TOTAL NUMBER OF RECORDS FETCHED
BASED ON THE FILTERS INCLUDING
ALL PAGES */
)
AS
BEGIN
/* DECLARE LOCAL VARIABLES FOR FILTER CONDITIONS */
DECLARE @CarrierTrackingNumber VARCHAR(27), /* 2 CHARACTERS MORE THAN THE INPUT BECAUSE
WE WILL BE DOING A LIKE SEARCH */
@ProductName NVARCHAR(52),
@ModifiedDateFrom DATETIME,
@ModifiedDateTo DATETIME
SELECT @CarrierTrackingNumber = '%' + @in_CarrierTrackingNumber + '%',
@ProductName = '%' + @in_ProductName + '%',
@ModifiedDateFrom = @in_ModifiedDateFrom,
@ModifiedDateTo = @in_ModifiedDateTo
/* THE TABLE BELOW WILL STORE THE PRIMARY KEYS TO ALL THE RECORDS THAT SATISFY THE FILTER
CRITERIA, SORTED IN THE ORDER REQUIRED. THIS IS NEEDED TO FIND THE TOTAL RECORDS FETCHED
BY THE FILTER CRITERIA */
DECLARE @SORTED_DETAILS_LIST TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
ORDER_ID INT,
DETAIL_ID INT
)
INSERT INTO @SORTED_DETAILS_LIST
(
ORDER_ID,
DETAIL_ID
)
SELECT
SalesOrderID,
SalesOrderDetailID
FROM
Sales.SalesOrderDetail OD
INNER JOIN Production.Product PR
ON
OD.ProductID = PR.ProductID
WHERE
OD.SalesOrderID = ISNULL(@in_SalesOrderID,OD.SalesOrderID)
AND OD.CarrierTrackingNumber LIKE @CarrierTrackingNumber
AND PR.Name LIKE @ProductName
AND OD.ModifiedDate BETWEEN @ModifiedDateFrom AND @ModifiedDateTo
ORDER BY
/* WE NEED TO CONVERT ALL THE COLUMNS THAT CAN BE QUALIFY FOR
A SORT POSITION TO HAVE THE SAME DATATYPE. SO CONVERTING
ALL OF THEM TO VARCHAR */
CASE WHEN @OrderBy = 'PRODUCT_NAME' AND @SortOrder <> 'DESC'
THEN CAST(PR.Name AS VARCHAR(50))
WHEN @OrderBy = 'TRACKING_NBR' AND @SortOrder <> 'DESC'
THEN OD.CarrierTrackingNumber
WHEN @OrderBy = 'MODIFIED_DATE' AND @SortOrder <> 'DESC'
THEN CONVERT(VARCHAR,OD.ModifiedDate,102)
ELSE NULL
END ASC,
/* THE SAME HAS TO BE REPEATED FOR DESCENDING SORT ORDER */
CASE WHEN @OrderBy = 'PRODUCT_NAME' AND @SortOrder = 'DESC'
THEN CAST(PR.Name AS VARCHAR(50))
WHEN @OrderBy = 'TRACKING_NBR' AND @SortOrder = 'DESC'
THEN OD.CarrierTrackingNumber
WHEN @OrderBy = 'MODIFIED_DATE' AND @SortOrder = 'DESC'
THEN CONVERT(VARCHAR,OD.ModifiedDate,102)
ELSE NULL
END DESC,
/* WE WILL ALWAYS SORT BY ORDER_ID AND DETAIL_ID EVEN IF NO SORT CONDITIONS ARE SPECIFIED */
CASE WHEN @SortOrder <> 'DESC'
THEN OD.SalesOrderID ELSE NULL END ASC,
CASE WHEN @SortOrder = 'DESC'
THEN OD.SalesOrderID ELSE NULL END DESC,
CASE WHEN @SortOrder <> 'DESC'
THEN OD.SalesOrderDetailID ELSE NULL END ASC,
CASE WHEN @SortOrder = 'DESC'
THEN OD.SalesOrderDetailID ELSE NULL END DESC
/* STORE THE TOTAL RECORDS SELECTED BY THE LAST QUERY INTO THE OUTPUT PARAMETER */
SET @TotalRecCount = @@ROWCOUNT
/* FETCH THE COMPLETE DATA TO BE RETURNED BACK ONLY FOR THOSE ORDERS THAT ARE SELECTED BASED
ON FILTER AND PAGINATION REQUIREMENT */
SELECT
SDL.ID AS ROW_ID,
OD.SalesOrderID AS ORDER_ID,
OD.SalesOrderDetailID AS DETAIL_ID,
OD.CarrierTrackingNumber AS TRACKING_NBR,
OD.OrderQty AS ORDERED_QTY,
PR.Name AS PRODUCT_NAME,
OD.SpecialOfferID AS SPECIAL_OFFER,
OD.UnitPrice AS UNIT_PRICE,
OD.UnitPriceDiscount AS DISCOUNT,
OD.LineTotal AS TOTAL_COST,
OD.ModifiedDate AS MODIFIED_DATE
FROM
@SORTED_DETAILS_LIST SDL
INNER JOIN Sales.SalesOrderDetail OD
ON
SDL.ORDER_ID = OD.SalesOrderID
AND SDL.DETAIL_ID = OD.SalesOrderDetailID
INNER JOIN Production.Product PR
ON
OD.ProductID = PR.ProductID
WHERE
SDL.ID BETWEEN (@TargetPage-1)*@PageSize + 1 AND (@TargetPage)*@PageSize
ORDER BY
SDL.ID ASC
END
DECLARE @OutTotalRecCount int
EXEC SalesOrderDetailCustomSort @in_ModifiedDateFrom = '2003-01-01',
@in_ProductName = 'Tour',
@OrderBy = 'TRACKING_NBR',
@PageSize = 25,
@TargetPage = 8,
@TotalRecCount = @OutTotalRecCount OUTPUT
SELECT @OutTotalRecCount AS TOTAL_RECORDS_FETCHED
This is the result set that is returned back:
Posts
0 comments:
Post a Comment