SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ContactsSelCollection
	@Page INT = NULL,
	@PageSize INT = NULL,
	@Sort NVARCHAR(45) = NULL,
	@Direction BIT = NULL,
	@TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @StartNum int, @EndNum int

	IF @Page IS NULL SELECT @Page = 1
	IF @PageSize IS NULL SELECT @PageSize = 50
	IF @Direction IS NULL SELECT @Direction = 0
	IF @Sort IS NULL SELECT @Sort = 'CreatedDate'

	SELECT @Page = @Page - 1
	SELECT @StartNum = @Page * @PageSize + 1, @EndNum = (@Page + 1) * @PageSize

	SET @TotalCount = (SELECT COUNT(*) FROM dbo.Contacts WITH (NOLOCK) WHERE IsActive = 1)

	SELECT *
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY
		CASE WHEN @Direction = 1 AND @Sort = 'FirstName' THEN FirstName END ASC, CASE WHEN @Direction = 0 AND @Sort = 'FirstName' THEN FirstName END DESC,
		CASE WHEN @Direction = 1 AND @Sort = 'LastName' THEN LastName END ASC, CASE WHEN @Direction = 0 AND @Sort = 'LastName' THEN LastName END DESC,
		CASE WHEN @Direction = 1 AND @Sort = 'City' THEN City END ASC, CASE WHEN @Direction = 0 AND @Sort = 'City' THEN City END DESC,
		CASE WHEN @Direction = 1 AND @Sort = 'CreatedDate' THEN CreatedDate END ASC, CASE WHEN @Direction = 0 AND @Sort = 'CreatedDate' THEN CreatedDate END DESC
		) AS RowNum,
		ContactID, FirstName, LastName, Phone, Address, City, State, Zip, Country, Company, Website, EmailAddress, CreatedDate, ModifiedDate
		FROM dbo.Contacts
		WHERE IsActive = 1
	) x
	WHERE x.RowNum BETWEEN @StartNum AND @EndNum
END
GO