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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s