Sample Get Collection Store Procedure

ContactsSelCollection.sql

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