• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

sql server 2012 - OFFSET and FETCH clauses

Hi experts, can you gime an store procedure with

We recommend using OFFSET and FETCH clauses instead of the TOP clause to implement a paging solution query and limit the number of rows sent to a client application
0
enrique_aeo
Asked:
enrique_aeo
1 Solution
 
Ephraim WangoyaCommented:
Here is an example,
CREATE PROCEDURE procGetPagedData(@pageno int, @pagesize int) 
	AS
BEGIN
	DECLARE @offset INT
	SET @offset = @pageno * @pagesize
	
	SELECT * 
	FROM Employees 
	ORDER BY EMPID OFFSET @offset ROWS FETCH NEXT @pagsize ROWS ONLY;
END

Open in new window

0
 
enrique_aeoAuthor Commented:
CREATE PROCEDURE procGetPagedData(@pageno int, @pagesize int)
      AS
BEGIN
      DECLARE @offset INT
      SET @offset = @pageno * @pagesize

      SELECT orderid, custid, empid, orderdate
      FROM Sales.Orders
      ORDER BY orderdate, orderid DESC
      OFFSET @offset ROWS FETCH NEXT @pagesize ROWS ONLY;
END
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now