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