SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Paging In Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
57 Posts

Posted - 05/14/2013 :  05:44:33  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/14/2013 :  05:54:03  Show Profile  Reply with Quote
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
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/16/2013 :  02:29:43  Show Profile  Reply with Quote
-- 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;

Edited by - UnemployedInOz on 05/16/2013 03:44:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/16/2013 :  02:44:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000