Paging In Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Paging In Stored Procedure

Author

Topic

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-05-14 : 05:44:33

Im creating an Application In vb.net winforms
now im using stored procedure.. i'm working with over 1000 records in one table..
and when viewing that records in a datagridview in vb.net2010, the system slows downs because of the number of records viewed

i figured out that PAGINATION will solve my problem..
ive used the TOP(n) CLAUSE but the it does not fit as my solution hence it cannot show records other than the n Specified ..

any help building my idea will be entertained or any link to any solution will be entertained and i'd be thankful for it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 05:54:03

Sorry didnt understand whats the issue with TOP n? Provided you wrote ORDER BY based on correct columns it should still fetch you your required records.

As you've posted on 2012, another new features available for you is using OFFSET... FETCH clause along with ORDER BY which is available from 2012 onwards

see more details below

http://www.mssqltips.com/sqlservertip/2362/overview-of-offset-and-fetch-feature-of-sql-server-2012/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-05-16 : 02:29:43

-- some examples I think you may be trying to get to
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY;

-------------------------------------------------------------
DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 3;

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 02:44:19

quote:
Originally posted by UnemployedInOz

-- some examples I think you may be trying to get to
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY;

-------------------------------------------------------------
DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 3;

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET (~~@pagesize~~@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

slight tweak to make output correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources