Sortable Paging Stored Procedure for SQL Server 2000 and SQL Server 2005

Posted on March 7, 2007 by robkraftmsn

Scott Mitchell and Greg Hamilton posted an excellent example for a stored procedure to support paging on web sites in this article: http://www.4guysfromrolla.com/webtech/041206-1.shtml.   Here, I have taken their best example one step further and added support for sorting as well.  The program can now pass the column to sort by to the stored procedure (along with ASC or DESC if desired.  The only complex piece to this was that in having a dynamic sort column, the bulk of the SQL to execute must be dynamic.  Note that I was unable to use the optimal stored proc by Greg as a starting point because the user could choose to sort on any column which could include rows with null values, and that would throw off the results.

 

CREATE

PROCEDURE [dbo].[usp_PagedResults_New_Rowcount_Sortable]

(

@startRowIndex int,

@maximumRows int,
@sortingColumn varchar(30)
)

AS

 
DECLARE

@maxRow int

— A check can be added to make sure @startRowIndex isn’t > count(1)
— from employees before doing any actual work unless it is guaranteed
— the caller won’t do that
SET

@maxRow = (@startRowIndex + @maximumRows) – 1

SET

ROWCOUNT @maxRow

exec

(‘

DECLARE @TempItems TABLE
(
ID int IDENTITY,
EmployeeID int
)
INSERT INTO @TempItems (EmployeeID)
SELECT EmployeeID
FROM Employees
ORDER BY ‘

+ @sortingColumn + ‘

SET ROWCOUNT ‘

+ @maximumRows + ‘

SELECT t.ID, e.*, d.[Name] as DepartmentName
FROM @TempItems t
INNER JOIN Employees e ON
e.EmployeeID = t.EmployeeID
INNER JOIN Departments d ON
d.DepartmentID = e.DepartmentID
WHERE ID >= ‘

+ @startRowIndex)

SET

ROWCOUNT 0

 
 
 
This entry was posted in SQL Server. Bookmark the permalink.

2 Responses to Sortable Paging Stored Procedure for SQL Server 2000 and SQL Server 2005

  1. Unknown says:
    March 10, 2008 at 7:15 am

    Be carful.  The above code will open the SP to SQL injection.Regards,Richard

    Reply
  2. Rob says:
    March 10, 2008 at 4:48 pm

    Of course any stored proc risks SQL injection if a string is passed in; and it is used as part of a dynamic SQL.  My development always uses ADO.Net now, and ADO.Net does a really good job of preventing SQL Injection; especially when ado.net parameters are used. 🙂

    Reply

Leave a comment