SQL Server paging Stored Procedure

Createproc [dbo]. [Signature] @ TableNamevarchar (4000), @ PkFieldvarchar (100), @ PageIndexint1, @ PageSizeint10, @ SqlWherenvarchar (4000), @ rowcountbig1_utput, @ resolve (@ SQL

Create proc [dbo]. [proc_Opinion_BaseInfo] @ TableName varchar (4000), @ PkField varchar (100), @ PageIndex int = 1, @ PageSize int = 10, @ SqlWhere nvarchar (4000 ), @ RowCount bigint output, @ PageCount bigint output as if (@ SQL

SQL Server paging Stored Procedure

Create proc [dbo]. [proc_Opinion_BaseInfo]

@ TableName varchar (4000 ),

@ PkField varchar (100 ),

@ PageIndex int = 1,

@ PageSize int = 10,

@ SqlWhere nvarchar (4000 ),

@ RowCount bigint output,

@ PageCount bigint output

As

If (@ SqlWhere = '1 ')

Set @ SqlWhere = '1 = 1'

Declare @ SQL nvarchar (4000), @ start int, @ end int

Set @ SQL = 'select * from (select Row_NUMBER () OVER (order by '+ @ PkField + 'desc) rowId, * from' + @ TableName + 'where' + @ SqlWhere

Set @ start = (@ PageIndex-1) * @ PageSize + 1

Set @ end = @ start + @ PageSize-1

Set @ SQL = @ SQL + ') t where rowId between' + CAST (@ start as varchar (20) + 'and' + CAST (@ end as varchar (20 ))

Exec (@ SQL)

Set @ SQL = 'select @ RowCount = count (1) from '+ @ TableName + 'where' + @ SqlWhere

Exec sp_executesql @ SQL, n' @ RowCount bigint output', @ RowCount OUTPUT

If (@ RowCount % @ PageSize = 0)

Begin

Set @ PageCount = @ RowCount/@ PageSize

End

Else

Begin

Set @ PageCount = @ RowCount/@ PageSize + 1

End