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!!!
--- 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
Consider statement as query
SELECT [Order Details].UnitPrice, [Order Details].ProductID, Products.ProductName,
Products.CategoryID, Products.SupplierID, Categories.Description, Categories.CategoryName,Categories.Picture
FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
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)