Create PROCEDURE [dbo]. [Proc_splitpage]
@tblName varchar (255),--table name
@strFields varchar (1000) = ' * ',--the column that needs to be returned, default *
@strOrder varchar (255) = ',--sort field name, required
@strOrderType varchar = ' ASC ',--sort the way, default ASC
@PageSize int = 10,--page size, default 10
@PageIndex int = 1,---page number, default 1
@strWhere varchar (1500) = "--Query criteria (note: Do not add where)
As
DECLARE @strSQL varchar (5000)
If @strWhere! = "
Set @strWhere = ' where ' [email protected]
Set @strSQL =
' SELECT * FROM (' +
' SELECT row_number () over (ORDER by ' [email protected]+ ' [email protected]+ ') as POS, ' [email protected]+ ' +
' From ' [email protected]+ ' [email protected]+
') as SP WHERE pos between ' +str ((@PageIndex-1) * @PageSize + 1) + ' and ' +str (@PageIndex * @PageSize)
EXEC (@strSQL)
. NET SQL Server paging stored procedures