Sorting and Paging Recordsets in SQL Server
Here's a stored procedure that I use for sorting and paging large recordsets in SQL Server, as opposed to using the more common and terribly inefficient entire dataset approach in .NET. It certainly doesn't matter much in the little classroom examples of a few hundred, or even thousands, of records, but working with larger recordsets with datasets is just bad. This is even more true with ASP.NET, since the entire dataset is usually saved in viewstate, and since the web server is doing the processing for many users. This stored procedure is certainly not the only way to do sorting and paging in SQL Server, but it is probably the most efficient, although proper indexing is still required to avoid table scans. Note that this does not use temporary tables, which are convenient but not as optimal -- this also means you could modify this to be dynamic SQL for Access or other databases! The parameters are the name of the table, the name of the primary key (necessary for the little bit of optimization included), the name of the sort field (or sort fields, with or without ASC/DESC), and the page size (number of records). It also allows optional parameters for the page index (defaults to 1st page) and a query filter (defaults to null) so you can sort and page through filtered records only! Note that it returns two recordsets -- the first is the results you expect, and the second recordset is a scalar with the number of total pages so you can better define the GUI pager for a grid. This is relatively easy to integrate with the ASP.NET datagrid if you use custom paging and sorting, and it will minimize both your load on the server and the amount of data sent to the client's browser! By the way, this code is just modified from some I found on the net, and there are certainly some minor optimizations that can be done, like using different sql for the first page.
CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)
END
ELSE
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END
RETURN 0
GO