Paging and Sorting
I am using this procedure to extract records from a table
ALTER PROC [dbo].[dbWBS_usp_DisplayCertificatesDetails]
(
@PageNum BIGINT=1,
@PageSize BIGINT=10,
@OrderBy VARCHAR(50)='Certificate_Id',
@TotalRowsNum BIGINT=0 OUTPUT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Query VARCHAR(1024);
SET @Query = 'SELECT * FROM
(
SELECT ''ID1'' = ROW_NUMBER() OVER (ORDER BY '+ @OrderBy +'),Certificate_ID, Issue_Authority, Cert_Type,
Req_Authority, CONVERT(VARCHAR(12), Install_Date, 109) AS Install_Date,
CONVERT(VARCHAR(12), Expire_Date, 109) AS Expire_Date,
CONVERT(VARCHAR(12), Notify_Date, 109) AS Notify_Date,
(SELECT Dept_name FROM tbl_Department_Master where dept_id = Department) as DepartmentName
FROM tbl_Certificate_Master
WHERE IsDelete = 0
)
AS Temp WHERE ID1 BETWEEN '+CAST(((@PageNum - 1) * @PageSize + 1) AS VARCHAR(10)) +' AND '+
CAST((@PageNum * @PageSize)AS VARCHAR(10)) + ''
-- Execute the Query
EXEC (@Query)
-- Return Total Records Numbers
SELECT @TotalRowsNum=COUNT(Certificate_ID) FROM tbl_Certificate_Master WHERE IsDelete = 0
END
With this procedure the paging is working absolutely fine. At a time 5 records get displayed in a gridview. but the problem with sorting is it is sorting the entire records, I only want the 5 records being displayed to be sorted. How can I accompalish this??
# 1 Re: Paging and Sorting
You want to order the filtered set rather than the source set, so you simply put your ORDER BY on the second SELECT statement.
Building a string for a query is pretty bad for performance because the stored procedure can't be optimized as it would if it was SQL. I know it seems like a good idea because you can enter a string for which column to order by, but I would rather do it like this:
ALTER PROCEDURE [dbo].[dbWBS_usp_DisplayCertificatesDetails]
@Page smallint = 1,
@RowsPerPage tinyint = 10,
@OrderMode tinyint = 0,
@TotalRows smallint = NULL OUTPUT
AS
SELECT @TotalRows = COUNT(*) FROM tbl_Certificate_Master WHERE IsDelete = 0;
WITH List AS (
SELECT
TOP (@Page * @RowsPerPage)
CASE @OrderMode
WHEN 0 THEN ROW_NUMBER() OVER (ORDER BY cm.Certificate_Id)
WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY cm.Install_Date DESC)
-- ...
END AS Row,
cm.Certificate_ID, cm.Issue_Authority, cm.Cert_Type, cm.Req_Authority,
CONVERT(varchar(12), cm.Install_Date, 109) AS Install_Date,
CONVERT(varchar(12), cm.Expire_Date, 109) AS Expire_Date,
CONVERT(varchar(12), cm.Notify_Date, 109) AS Notify_Date,
dm.Dept_name AS DepartmentName
FROM tbl_Certificate_Master cm
LEFT JOIN tbl_Department_Master dm ON dm.Dept_id = cm.Department
WHERE IsDelete = 0
ORDER BY Row
)
SELECT
Certificate_ID, Issue_Authority, Cert_Type, Req_Authority, Install_Date, Expire_Date,
Notify_Date, DepartmentName
FROM List
WHERE Row > (@Page - 1) * @RowsPerPage;
This creates a temporary view that only gets the rows for the specified page (it also has a TOP statement to stop retrieving rows it won't use.) Instead of entering the name of the column you will order by, it chooses the order based on a number instead. This allows you to use only SQL which makes the query run faster. In my example above I order the source set and not the filtered set, but you get the idea.
