ALTER PROCEDURE [dbo].[procSelectUsers] (
	@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy 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 CHARINDEX(@p_vchLetterFilter, vchLastName, 0) = 1)
			
			

	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' THEN (RANK() OVER (ORDER BY vchLastName, vchFirstName))
			WHEN @p_vchOrderBy = 'first' THEN (RANK() OVER (ORDER BY vchFirstName, vchLastName))
			WHEN @p_vchOrderBy = 'username' THEN (RANK() OVER (ORDER BY vchUserName))
			WHEN @p_vchOrderBy = 'security' THEN (RANK() OVER (ORDER BY vchSecurityRole, vchLastName, vchFirstName))
			WHEN @p_vchOrderBy = 'workgroup' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc, vchLastName, vchFirstName))
		END
	
END /* CREATE PROCEDURE procSelectAllUsers */