Paging in stored procedures

Pass two additional parameters to you stored procedure and perform where condition like this

WHERE rowNumber BETWEEN @skip + 1 AND @skip + @take

Example

@take int = 20
@skip int = 0

;WITH orders AS (
SELECT 
   OrderId,
   InvoiceNumber
    ROW_NUMBER() OVER (ORDER BY OrderId) AS rowNumber
FROM dbo.Orders
)

SELECT 
OrderId,
InvoiceNumber,
(SELECT COUNT(*) FROM orders) as TotalRows
FROM orders
WHERE rowNumber BETWEEN @skip + 1 AND @skip + @take