Paging in SQL Server by using TSQL

• Download source files - 4.00 Kb

Introduction

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 :) 

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><P> </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><P class=MsoNormal style="MARGIN: 0in 0in 0pt">Categories.Picture FROM [Order Details] INNER JOIN Products </P><P class=MsoNormal style="MARGIN: 0in 0in 0pt">ON [Order Details].ProductID = Products.ProductID INNER JOIN Categories </P><P class=MsoNormal style="MARGIN: 0in 0in 0pt">ON Products.CategoryID = Categories.CategoryID 
</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