SQL Paging Stored Procedures

Use [DBName]
GO
/****** object:storedprocedure [dbo]. [Up_getrecordbypage] Script date:12/05/2012 16:17:11 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo]. [Up_getrecordbypage]
@tblName varchar (255),--table name
@fldName varchar (255),--primary key field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page
@RowCount int output,--Returns the total number of records
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (1000) = "--Query criteria (note: Do not add where)
As

DECLARE @strSQL varchar (6000)--the subject sentence
DECLARE @strTmp varchar (100)--Temporary variable
DECLARE @strOrder varchar (400)--Sort type

If @OrderType! = 0
Begin
Set @strTmp = ' < (select Min ')
Set @strOrder = ' ORDER by [' + @fldName + '] desc '
End
Else
Begin
Set @strTmp = ' > (select Max ')
Set @strOrder = ' ORDER by [' + @fldName + '] ASC '
End

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * FROM ['
+ @tblName + '] where [' + @fldName + '] ' + @strTmp + ' (['
+ @fldName + ']) from (select top ' + str (@PageIndex-1) * @PageSize) + ' ['
+ @fldName + '] from ' + @tblName + ' + @strOrder + ') as Tbltmp) '
+ @strOrder

If @strWhere! = "
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * from '
+ @tblName + ' WHERE [' + @fldName + '] ' + @strTmp + ' (['
+ @fldName + ']) from (select top ' + str (@PageIndex-1) * @PageSize) + ' ['
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + '
+ @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder

If @PageIndex = 1
Begin
Set @strTmp = ' '
If @strWhere! = "
Set @strTmp = ' where ' + @strWhere

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * from '
+ @tblName + "+ @strTmp +" + @strOrder
End

DECLARE @sql NVARCHAR (400)

Set @sql = N ' Select @RowCount =count (*) from ' [Email protected]+ ' WHERE ' + @strWhere
EXEC sp_executesql @sql, N ' @RowCount int out ', @RowCount out

EXEC (@strSQL)

GO

The second type:

if exists (SELECT * from sys.objects where name= ' proc_getrecordbypages ')
drop proc Proc_getrecordbypages
Go
CREATE PROCEDURE Proc_getrecordbypages
@PageIndex Int=1,
@PageSize int=10,
@PageCount int=1 Output
As
declare @StartIndex int, @EndIndex int
Set @StartIndex = (@PageIndex-1) * @PageSize +1
Set @[email protected] * @PageSize
SELECT * FROM
(
Select Row_number () over (order by p_order ASC) as sno,* from App_product
) as Tb1
Where Sno between @StartIndex and @EndIndex
Set @PageCount = (select COUNT (1) from app_product)
Go

DECLARE @PageCount int
exec proc_getrecordbypages 1,20, @PageCount output
Select @PageCount

SQL Paging Stored Procedures