Oct 23, 2009

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
4) The default sorting will be based on ORDER_ID, DETAIL_ID
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

When I execute the SP with the following parameters:

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:

0 comments:

Post a Comment