我的分页存储过程
CREATE PROCEDURE [dbo].[proc_DataPageList2000](
@tableName varchar(255),
@getFields varchar(1000) = '*',
@keyId varchar(50)='Id',
@strOrder varchar(255)='',
@strWhere varchar(1500) ='',
@pageIndex INT,
@pageSize INT,
@isCount INT = 0
) AS
SET NOCOUNT ON
DECLARE @strSql varchar(8000)
DECLARE @tempRowCount INT
DECLARE @totalPages INT
DECLARE @currentPageSize INT
DECLARE @strOrder2 varchar(200)
DECLARE @rowCount int
IF @pageIndex < 1
BEGIN
RAISERROR ('传入的页码或每页记录数大小参数错误!', 16, 1)
RETURN
END
IF NOT (@strWhere is null or RTRIM(@strWhere)='')
SET @strWhere = ' WHERE '+@strWhere
IF NOT (@strOrder is null or RTRIM(@strOrder)='')
SET @strOrder = ' ORDER BY '+@strOrder
Else
SET @strOrder = ' ORDER BY '+@keyId+' DESC'
declare @isgroupby int
if charindex('group by', lower(@strWhere))>0 begin
set @isgroupby=1
end else begin
set @isgroupby=0
end
IF(@isCount=1)
BEGIN
set @strSql='select count(*) as TotalCount from '+@tableName+@strWhere
if @isgroupby=1 begin
set @strSql='select count(*) as TotalCount from (select count(1) as a from '+@tableName+@strWhere + ') as tt1'
end
EXEC(@strSql)
END
Else
BEGIN
IF(@pageIndex=1)
IF @pageSize=-1 BEGIN
SET @strSql = 'SELECT '+@getFields
SET @strSql = @strSql +' FROM ['+@tableName+'] WITH(NOLOCK) '+@strWhere+@strOrder
End
ELse
Begin
SET @strSql = 'SELECT TOP '+CONVERT(VARCHAR(10),@pageSize)+' '+@getFields
SET @strSql = @strSql +' FROM ['+@tableName+'] WITH(NOLOCK) '+@strWhere+@strOrder
END
ELSE
BEGIN
set @strOrder = UPPER(@strOrder)
set @strOrder2 = replace(@strOrder,'DESC','DESC1')
set @strOrder2 = replace(@strOrder2,'ASC','DESC')
set @strOrder2 = replace(@strOrder2,'DESC1','ASC')
declare @sql nvarchar(500)
set @sql ='select @maxCount = count(*) from [' + @tableName + ']' + @strWhere
if @isgroupby=1 begin
set @sql='select @maxCount = count(*) from (select count(1) as a from '+@tableName+@strWhere + ') as tt2'
end
exec sp_executesql @sql,N'@maxCount int output',@rowCount output
SET @totalPages = CASE WHEN @rowCount%@pageSize =0 THEN @rowCount / @pageSize ELSE @rowCount/@pageSize + 1 END
IF(@pageIndex >= @totalPages)
BEGIN
SET @currentPageSize = @rowCount - (@pageIndex -1) * @pageSize
WHILE(@currentPageSize < 0)
BEGIN
SET @currentPageSize = @pageSize + @currentPageSize
END
SET @strSql = 'SELECT * from ('+char(13)
SET @strSql = @strSql+'SELECT TOP '+ CONVERT(VARCHAR(10),@currentPageSize)+' '+ @getFields +' FROM
['+@tableName+'] WITH(NOLOCK) '+@strWhere+@strOrder2+char(13)
SET @strSql = @strSql+') as a '+@strOrder
END
ELSE
BEGIN
IF(@pageIndex <= @totalPages/2)
BEGIN
SET @tempRowCount=@pageIndex*@pageSize
SET @strSql = 'SELECT * from ('+char(13)
SET @strSql = @strSql+'SELECT TOP '+CONVERT(VARCHAR(10),@pageSize)+' * FROM ('+char(13)
SET @strSql = @strSql+' SELECT TOP '+CONVERT(VARCHAR(10),@tempRowCount)+' '+ @getFields +' FROM
['+@tableName+'] WITH(NOLOCK) '+@strWhere+@strOrder
SET @strSql = @strSql+') as a1 '+@strOrder2+char(13)
SET @strSql = @strSql+') as a '+@strOrder
END
ELSE
BEGIN
SET @tempRowCount=@rowCount - (@pageIndex -1)*@pageSize
SET @strSql = 'SELECT * from ('+char(13)
SET @strSql = @strSql+'SELECT TOP '+CONVERT(VARCHAR(10),@pageSize)+' * FROM ('+char(13)
SET @strSql = @strSql+' SELECT TOP '+CONVERT(VARCHAR(10),@tempRowCount)+' '+ @getFields +' FROM
['+@tableName+'] WITH(NOLOCK) '+@strWhere+@strOrder2
SET @strSql = @strSql+') as a1 '+@strOrder+char(13)
SET @strSql = @strSql+') as a '
END
END
END
EXEC(@strSql)
END
