SQL代码
- CREATE Procedure Pagination
- (
- @TableName nvarchar(50), /*table name*/
- @PKey nvarchar(50), /*primary key default id*/
- @FieldList nvarchar(250), /*search primary*/
- @Condition nvarchar(250), /*Condition Like: And a=1*/
- @OrderBy nvarchar(250), /*Order By id*/
- @Sql nvarchar(1000), /**/
- @SqlGetRC nvarchar(1000), /**/
- @CurrPage int, /*CurrPage*/
- @PageSize int, /*PageSize*/
- @RecordCount int
- )
- AS
- Declare @PageCount int
- if @SqlGetRC=''
- Set @SqlGetRC = 'SELECT @RecordCount=COUNT(0) FROM ' + @TableName + @Condition
- if @RecordCount=-1
- exec sp_executesql @SqlGetRC,N'@RecordCount int out',@RecordCount out
- Set @PageCount = (@RecordCount + @PageSize - 1) / @PageSize
- if @CurrPage>@PageCount And @PageCount>0
- Set @CurrPage=@PageCount
- if @Sql = ''
- Begin
- if @PageSize=0
- Set @PageSize = 10
- if @CurrPage=1
- Set @Sql = 'SELECT TOP ' + Cast(@PageSize as nvarchar) + ' ' + @FieldList + ' FROM ' + @TableName + @Condition + ' ' + @OrderBy
- else
- Set @Sql = 'SELECT TOP ' + Cast(@PageSize as nvarchar) + ' ' + @FieldList + ' FROM ' + @TableName + ' WHERE ' + @Pkey + ' NOT IN (SELECT TOP ' + Cast((@CurrPage-1)*@PageSize as nvarchar) + ' ' + @Pkey + ' FROM ' + @TableName + ' ' + @Condition + ' ' + @OrderBy + ') ' + replace(@Condition,' WHERE 1=1',' ') + ' ' + @OrderBy
- End
- exec(@Sql)
- Select RecordCount=@RecordCount,PageCount=@PageCount
- GO
