聲明：根據(jù)別人改編的
---------------------------------------------------------------------------------------------------------------------
--準備工作（創(chuàng)建數(shù)據(jù)庫、表、插入二百萬數(shù)據(jù)，建議采用本地數(shù)據(jù)庫）
Create database data_Test

GO

use data_Test

GO
create table tb_TestTable --創(chuàng)建表
(

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
--插入數(shù)據(jù)：
--qlserver 批量插入記錄時,對有標識列的字段要設(shè)置 set IDENTITY_INSERT 表名 on,
--然后再執(zhí)行插入記錄操作;插入完畢后恢復(fù)為 off 設(shè)置
--格式:
-- 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, --當(dāng)前頁碼
@PageSize int = 10, --每頁記錄數(shù)
@Fields nvarchar(1000) = N'*', --輸出字段
@Filter nvarchar(1000) = NULL, --where過濾條件(不帶where)
@Group nvarchar(1000) = NULL, --Group語句(不帶Group By)
@TotalCount int OUTPUT --總記錄數(shù)
)
AS

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

--設(shè)定排序語句
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

--設(shè)置排序字段類型和精度
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))
--設(shè)置開始分頁記錄數(shù)
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 '
--計算總記錄數(shù)
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
--執(zhí)行查詢語句
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

)
----------------------------------------------------------------------------------------------------------------------
--實際執(zhí)行代碼：
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
----------------------------------------------------------------------------------------------------------------------
--執(zhí)行存儲過程：
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
-----------------------------------------------------------------------------------------------------------------------