Click here to Skip to main content
11,934,389 members (66,059 online)
Click here to Skip to main content
Add your own
alternative version


47 bookmarked

Paging in SQL Server by using TSQL

, 18 May 2006
Rate this:
Please Sign up or sign in to vote.
Facilitating efficient Sql Server paging by giving T-SQL query as input to this generic stored procedure

Sample Image - Easy_Sqlserver_paging.jpg


Generally, developers specially beginners or application developer face problems to implement paging in web or desktop based application applications.

Two concepts of paging are available:  

-At Database level 
-At Application level

Most of the developers apply paging on application layer which is not a very good idea for web centric applications, as it is always expensive to query database and then create page at BLL/DLL level.

Paging on database level i.e. in Stored Procedure generally requires more effort and time, that is why mostly developers are reluctant to go for this option.

To cut it short, I have created a generic stored procedure which will do all this hassle, and it is good enough to handle most of the queries with T-SQL syntax. Now you don't need to create separate Stored Procedure for paging Smile | :)  

Happing coding, cheers!!! 

<P>  --- Input Parameters
 @Tables varchar(1000),
 @PK varchar(100),
 @JoinStatements varchar(1000)='',
 @Fields varchar(5000) = '*',
 @Filter varchar(5000) = NULL,
 @Sort varchar(200) = NULL,
 @PageNumber int = 1,
 @PageSize int = 10,
 @TotalRec int =0 Output,
 @Group varchar(1000) = NULL</P>

Consider statement as query

<P class=MsoNormal style="MARGIN: 0in 0in 0pt">SELECT     [Order Details].UnitPrice, [Order Details].ProductID, Products.ProductName, 
Products.CategoryID, Products.SupplierID, Categories.Description, Categories.CategoryName,</P>



Sample variable assignment  

@tables specify first joining table

Which is [Order Details]
@PK is  [Order Details].OrderID

@JoinStatements is 
INNER JOIN  Products ON [Order Details].ProductID = Products.ProductID INNER JOIN 
Categories ON Products.CategoryID = Categories.CategoryID

@Fields is [Order 
Details].UnitPrice, [Order Details].ProductID, Products.ProductName, 
Products.CategoryID, Products.SupplierID, Categories.Description 
,Categories.CategoryName, Categories.Picture

@TotalRec will return how much records are in table soyou can calculate total pages  
Formula is simple CEILING (@TotalRec/@PageSize) 

And so on ..

Soon I will upload example c# code that will show how you can use it



This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

GeneralEasiest Paging Method Pin
mayurmv4-Sep-08 18:10
membermayurmv4-Sep-08 18:10 
Generalworks only if pk is integer Pin
mirani ajay19-Dec-06 6:42
membermirani ajay19-Dec-06 6:42 
QuestionHow about performance Pin
tfrost16-Aug-05 0:15
membertfrost16-Aug-05 0:15 
AnswerRe: How about performance Pin
Muhammad Kashif Akram17-Aug-05 10:34
memberMuhammad Kashif Akram17-Aug-05 10:34 
I am my self tried it and working wonderful with a big PrintMIS solution
Actually idea was to put less effort by application developer and also to loose performance

And it is working fine with me
Also you don’t have to make many store procedures for same task (listing data) on many various screens and sections

GeneralRe: How about performance Pin
Tzoockee29-Aug-05 2:42
memberTzoockee29-Aug-05 2:42 
GeneralRe: How about performance Pin
Muhammad Kashif Akram19-Sep-05 0:57
memberMuhammad Kashif Akram19-Sep-05 0:57 
GeneralRe: How about performance Pin
noeldr18-May-06 9:57
membernoeldr18-May-06 9:57 
AnswerRe: How about performance Pin
zvn14-Jul-06 4:36
memberzvn14-Jul-06 4:36 
GeneralGood Idea Pin
Sofyan_OPY15-Aug-05 19:58
memberSofyan_OPY15-Aug-05 19:58 
GeneralRe: Good Idea Pin
Muhammad Kashif Akram17-Aug-05 10:36
memberMuhammad Kashif Akram17-Aug-05 10:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151126.1 | Last Updated 18 May 2006
Article Copyright 2005 by Muhammad Kashif Akram
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid