** 千万数量级分页存储过程 **

/****** Object: StoredProcedure [dbo].[p_DataPagination] Script Date: 07/18/2017 15:14:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery22.sql|7|0|C:\Users\lizhg\AppData\Local\Temp\~vsCE70.sql
CREATE PROCEDURE [dbo].[p_DataPagination]
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图 , 多表查询可用视图做
2.PrimaryKey :主关键字
3.Fields :查询字段 , 默认为 *
4.Filter :过滤语句，不带Where
5.Group :Group语句,不带Group By
6.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
7.CurrentPage :当前页码
8.PageSize :分页尺寸
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@TotalRecordCount int out,
@PageCount int out
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @sql nvarchar(1000)
DECLARE @paramDefine nvarchar(100)
/******************** 数据表中总记录数 ***********************/
SET @sql = 'SELECT @TotalRecordCount = COUNT(*) FROM ' + @Tables + ' WHERE 1=1'
IF @Filter IS NOT NULL AND @Filter <> ''
SET @sql = @sql + ' AND ' + @Filter
SET @paramDefine = '@TotalRecordCount INT OUTPUT'
EXEC SP_EXECUTESQL @sql,@paramDefine,@TotalRecordCount OUTPUT

set @PageCount=(@TotalRecordCount+@pageSize-1)/@pageSize
/*************************************************************/

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
DECLARE @strRowNumber nvarchar(4000)

/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/

SET @strRowNumber= 'row_number() over(order by ' +@Sort+')'
EXEC(
'
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + '*' + ' FROM
(
SELECT ('+@strRowNumber+') as rownum,' + @Fields+
' FROM ' + @Tables + ' WHERE ( 1=1 ' + @strSimpleFilter + ')'+@strGroup+'
) temp
WHERE ( rownum >=' +@strStartRow+ ')'
)

return @TotalRecordCount