Thanks for the article. I'm working on a Query that returns a list of users, but I've never been able to do it with the ROW_NUMBER() because of the orderby. Here's what i have that works - SQL 2000 way. Is there any better way of writing this?

ALTER PROCEDURE [dbo].[procSelectUsers] (

@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int

)

AS

BEGIN

DECLARE @TempTable TABLE(RowID INT IDENTITY, UserID int)

INSERT INTO @TempTable(UserID)

SELECT intUserID

FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W

WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID

AND W.intWorkgroupID = U.intWorkgroupID

AND vchLastName LIKE @p_vchLetterFilter + '%'

AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'

ORDER BY

CASE

WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))

WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))

WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))

WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))

WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchUserName DESC))

WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchUserName ASC))

WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchRoleTitle DESC, vchLastName DESC, vchFirstName DESC))

WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchRoleTitle ASC, vchLastName ASC, vchFirstName ASC))

WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchDescription DESC, vchLastName DESC, vchFirstName DESC))

WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchDescription ASC, vchLastName ASC, vchFirstName ASC))

END

SELECTUserID, vchFirstName, vchLastName,vchUserName, vchPassword,

vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID,

IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber,

SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc

FROM @TempTable T, tblUsers U, tblSecurityRoles SR, tblWorkgroups W

WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID

AND T.UserID = U.intUserID

AND W.intWorkgroupID = U.intWorkgroupID

AND vchLastName LIKE @p_vchLetterFilter + '%'

AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'

AND t.RowID BETWEEN ((@p_intPage - 1) * @p_intPageSize + 1) AND (@p_intPage * @p_intPageSize)

END /* CREATE PROCEDURE procSelectAllUsers */

This is what I had before, but look at how it printed out the results - so it wouldn't work the way I needed:

ALTER PROCEDURE [dbo].[procSelectUsers] (

@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int

)

AS

BEGIN

DECLARE @startRowIndex INT

SET @startRowIndex = (@p_intPage * @p_intPageSize) + 1;

WITH Users as (

SELECT ROW_NUMBER() OVER (ORDER BY intUserID ASC) as Row,

intUserID, vchFirstName, vchLastName,vchUserName, vchPassword,

vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID,

IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber,

SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc

FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W

WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID

AND W.intWorkgroupID = U.intWorkgroupID

AND vchLastName LIKE @p_vchLetterFilter + '%'

AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'

)

SELECT

intUserID, vchFirstName, vchLastName,vchUserName, vchPassword,

vchEmail, intSecurityRoleID, intWorkgroupID, vchInitials, vchBadgeNumber,

vchSecurityRole, vchWorkgroupDesc

FROM Users

WHERE Row BETWEEN @startRowIndex AND @startRowIndex + @p_intPageSize - 1

ORDER BY

CASE

WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))

WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))

WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))

WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))

WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchUserName DESC))

WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchUserName ASC))

WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchSecurityRole DESC, vchLastName DESC, vchFirstName DESC))

WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchSecurityRole ASC, vchLastName ASC, vchFirstName ASC))

WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc DESC, vchLastName DESC, vchFirstName DESC))

WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc ASC, vchLastName ASC, vchFirstName ASC))

END

END /* CREATE PROCEDURE procSelectAllUsers */

EXEC [procSelectUsers] '', '', 'ASC', '', 0, 6

A1

A2

B1

B2

C1

C2

EXEC [procSelectUsers] '', '', 'ASC', '', 1, 6

A3

A4

B3

B4

C3

C4

Results show an example of the output - not the actual output