A simple solution if you need to return a paged data from SQL Server stored procedure

CREATE PROCEDURE [dbo].[XXX]
(
	@PageSize int = NULL,
	@PageNumber int = NULL
)
AS
BEGIN

	-- Calculate row number offsets for query.
	DECLARE @FirstRow INT, @LastRow INT
	SELECT @FirstRow = (@PageNumber - 1) * @PageSize + 1;
	SELECT @LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

	-- COMMON TABLE EXPRESSION TO PERFORM PAGING LOGIC
	WITH QueryResults AS
	(
		SELECT *,
			"RowNumber" = ROW_NUMBER() OVER (ORDER BY UserID ASC)
		FROM   Users C
	)
	SELECT	*
	FROM QueryResults
	WHERE RowNumber BETWEEN @FirstRow AND @LastRow
	ORDER BY RowNumber ASC
END
GO