STORED PROCEDURE FOR CUSTOM PAGING...

Hi EveryBody...
I am again here with my new post.The follwing post is for how to write a good stored procedure for custom paging.I m not saying that it is the best SP..can assure you that is good enough.the SP is here..


CREATE PROC USP_DEMO_PAGING
(
@UID INT,
@GridCurrentPageIndex INT,
@GridPageSize INT
)
AS
BEGIN

DECLARE @LastDisplayingRecordIndex int -- To store the index number of last displayed records in Grid in .NET page. For next page it will start by increment last index by 1.
DECLARE @MaxDisplayingRecordIndex int -- To store upto which index records should be display.

SET @LastDisplayingRecordIndex = @GridPageSize * (@GridCurrentPageIndex-1)
SET @MaxDisplayingRecordIndex = @GridPageSize * @GridCurrentPageIndex

-- VARIABLE DECLARATION FOR DYNAMIC QUERY PARTS TO BE USED IN PAGING

DECLARE @SelectQueryWhere varchar(500)-- Used to put the condtion of lower and higher record index
DECLARE @QueryCount varchar(max) -- Used to count the total records.

DECLARE @SelectMainQuery varchar(500) -- To store the selected column part of the SELECT statement
DECLARE @SelectMainQueryResultSet varchar(7000) -- To store the result set query with Row_Number
DECLARE @SelectWhereCreteria varchar(4000)
DECLARE @SelectJoinPart varchar(max)
DECLARE @SelectOrderByPart varchar(255)



SET @SelectQueryWhere = ') _Results WHERE RowNumber > ' + Convert(varchar,@LastDisplayingRecordIndex) + ' And RowNumber <= ' + Convert(varchar,@MaxDisplayingRecordIndex)
SET @SelectOrderByPart='' -- Please mentioned If any

SET @QueryCount = 'SELECT count(UID) from userImage' -- It Used for Counting all the Records

SET @SelectMainQuery ='SELECT ImageID,UID,USERNAME,USERIMAGE,ROW_NUMBER()over (order by ImageID) AS RowNumber from userImage'
SET @SelectJoinPart =''-- There is any Joins Please put here
SET @SelectWhereCreteria = ' where UId = '+ Convert(varchar,@UID)--Here You Can put Your Calculated Where Creteria
SET @SelectMainQueryResultSet = @SelectMainQuery + @SelectJoinPart + @SelectWhereCreteria
SET @SelectMainQueryResultSet = 'SELECT * FROM ('+ @SelectMainQueryResultSet + @SelectQueryWhere + @SelectOrderByPart
SET @QueryCount = @QueryCount + @SelectJoinPart + @SelectWhereCreteria

EXEC(@SelectMainQueryResultSet)
--PRINT @SelectMainQueryResultSet

EXEC(@QueryCount)

--PRINT @QueryCount
END


Happy Coding...
truely yours:
Sanjeev Chaudhary