数据库分页

声明：根据别人改编的
---------------------------------------------------------------------------------------------------------------------
--准备工作（创建数据库、表、插入二百万数据，建议采用本地数据库）
Create database data_Test

GO

use data_Test

GO
create table tb_TestTable --创建表
(

id int identity(1,1) PRimary key,
userName nvarchar(20) not null,
userPWD nvarchar(20) not null,
userEmail nvarchar(40) null ,
createtime datetime NOT NULL

)

GO
--插入数据：
--qlserver 批量插入记录时,对有标识列的字段要设置 set IDENTITY_INSERT 表名 on,
--然后再执行插入记录操作;插入完毕后恢复为 off 设置
--格式:
-- set IDENTITY_INSERT 表名 on
-- set IDENTITY_INSERT 表名 off
set identity_insert tb_TestTable on

declare @count int

SET @count=1

while @count<=2000000

begin

insert into tb_TestTable(id,userName,userPWD,userEmail,createtime) values(@count,'admin','admin888','lli0077@yahoo.com.cn',getdate())

set @count=@count+1

end

set identity_insert tb_TestTable off

---------------------------------------------分页存储过程------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_Paging]
(
@Tables nvarchar(1000), --表名/视图名
@PrimaryKey nvarchar(100), --主键
@Sort nvarchar(200) = NULL, --排序字段(不带order by)
@pageindex int = 1, --当前页码
@PageSize int = 10, --每页记录数
@Fields nvarchar(1000) = N'*', --输出字段
@Filter nvarchar(1000) = NULL, --where过滤条件(不带where)
@Group nvarchar(1000) = NULL, --Group语句(不带Group By)
@TotalCount int OUTPUT --总记录数
)
AS

DECLARE @SortTable nvarchar(100)
DECLARE @SortName nvarchar(100)
DECLARE @strSortColumn nvarchar(200)
DECLARE @Operator char(2)
DECLARE @type nvarchar(100)
DECLARE @prec int

--设定排序语句
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
IF CHARINDEX('DESC',@Sort) >0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '>='
END
ELSE
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

--设置排序字段类型和精度
SELECT @type=t.name, @prec=c.prec FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

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

IF @pageindex <1
SET @pageindex = 1
SET @strPageSize = CAST(@PageSize AS nvarchar(50))
--设置开始分页记录数
SET @strStartRow = CAST(((@pageindex-1)*@PageSize +1) AS nvarchar(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 '
--计算总记录数
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
--执行查询语句
EXEC(
'DECLARE @SortColumn ' + @type +
' SET ROWCOUNT ' + @strStartRow +
'SELECT @SortColumn=' + @PrimaryKey + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @PrimaryKey +
' SET ROWCOUNT ' + @strPageSize +
' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @PrimaryKey + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @PrimaryKey

)
----------------------------------------------------------------------------------------------------------------------
--实际执行代码：
DECLARE @sortcolumn nvarchar(100)
SET ROWCOUNT 151
SELECT @sortcolumn=id FROM dbo.tb_TestTable WHERE id>100 ORDER BY createtime
SET ROWCOUNT 15
PRINT @sortcolumn
SELECT * FROM dbo.tb_TestTable WHERE id>= @sortcolumn AND id>100 ORDER BY id
----------------------------------------------------------------------------------------------------------------------
--执行存储过程：
DECLARE @TotalCount int
--,@return_value int
EXEC [dbo].[sp_Paging]
@Tables = 'tb_TestTable',
@PrimaryKey = 'id',
@Sort = 'id ASC',
@pageindex = 11,
@PageSize = 15,
@Fields = '*',
@Filter = 'id>100',
@Group = NULL,
@TotalCount = @TotalCount OUTPUT

SELECT @TotalCount as N'@TotalCount'
--SELECT 'Return Value' = @return_value
-----------------------------------------------------------------------------------------------------------------------