--
创建测试表
if
object_id(
'
person
')
is
not
null
drop
table person
create
table person
(
id
int
primary
key
identity(
1,
1),
username
varchar(
50),
age
int,
remark
varchar(
100)
)
--
插入测试数据
declare
@i
int
set
@i
=
0
while
@i
<
100
begin
insert
into person
values(
'
zdw
'
+
convert(
varchar(
5),
@i),
@i,
convert(
varchar(
5),
@i))
set
@i
=
@i
+
1
end
go
select
*
from person
/**/
/*
* 分页存储过程
* 功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
* 查询可以指定页大小、指定查询任意页、指定输出字段列表
*/
CREATE
PROCEDURE sp_page
@tb
varchar(
50),
--
表名
@col
varchar(
50),
--
按该列来进行分页
@coltype
int,
--
@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby
bit,
--
排序,0-顺序,1-倒序
@collist
varchar(
800),
--
要查询出的字段列表,*表示全部字段
@selecttype
int,
--
查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
@pagesize
int,
--
每页记录数
@page
int,
--
指定页
@minid
varchar(
50),
--
当前页最小号
@maxid
varchar(
50),
--
当前页最大号
@condition
varchar(
800)
--
查询条件
AS
DECLARE
@sql
nvarchar(
4000),
@where1
varchar(
800),
@where2
varchar(
800)
DECLARE
@i
int,
@id
varchar(
50)
IF
@coltype
=
1
or
@coltype
=
2
--
字段类型为字符或日期时间要加上引号以作比较用
BEGIN
SET
@minid
=
''''
+
@minid
+
''''
SET
@maxid
=
''''
+
@maxid
+
''''
END
IF
@condition
is
null
or
rtrim(
@condition)
=
''
--
没有查询条件
BEGIN
SET
@where1
=
'
WHERE
'
SET
@where2
=
'
'
END
ELSE
--
有查询条件
BEGIN
SET
@where1
=
'
WHERE (
'
+
@condition
+
'
) AND
'
--
本来有条件再加上此条件
SET
@where2
=
'
WHERE (
'
+
@condition
+
'
)
'
--
原本没有条件而加上此条件
END
SET
@sql
=
CASE
@selecttype
WHEN
1
--
前页
THEN
'
SELECT * FROM (SELECT TOP
'
+
CAST(
@pagesize
AS
varchar)
+
'
'
+
@collist
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
CASE
@orderby
WHEN
0
THEN
'
<
'
+
@minid
ELSE
'
>
'
+
@maxid
END
+
'
ORDER BY
'
+
@col
+
CASE
@orderby
WHEN
0
THEN
'
DESC
'
ELSE
''
END
+
'
) t ORDER BY
'
+
@col
+
CASE
@orderby
WHEN
0
THEN
''
ELSE
'
DESC
'
END
WHEN
2
--
后页
THEN
'
SELECT TOP
'
+
CAST(
@pagesize
AS
varchar)
+
'
'
+
@collist
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
CASE
@orderby
WHEN
0
THEN
'
>
'
+
@maxid
ELSE
'
<
'
+
@minid
END
+
'
ORDER BY
'
+
@col
+
CASE
@orderby
WHEN
0
THEN
''
ELSE
'
DESC
'
END
WHEN
3
--
首页
THEN
'
SELECT TOP
'
+
CAST(
@pagesize
AS
varchar)
+
'
'
+
@collist
+
'
FROM
'
+
@tb
+
@where2
+
'
ORDER BY
'
+
@col
+
CASE
@orderby
WHEN
0
THEN
''
ELSE
'
DESC
'
END
WHEN
4
--
末页
THEN
'
SELECT * FROM (SELECT TOP
'
+
CAST(
@pagesize
AS
varchar)
+
'
'
+
@collist
+
'
FROM
'
+
@tb
+
@where2
+
'
ORDER BY
'
+
@col
+
CASE
@orderby
WHEN
0
THEN
'
DESC
'
ELSE
''
END
+
'
) t ORDER BY
'
+
@col
+
CASE
@orderby
WHEN
0
THEN
''
ELSE
'
DESC
'
END
END
IF
@selecttype
>=
1
and
@selecttype
<=
4
BEGIN
EXEC(
@sql)
RETURN
END
ELSE
BEGIN
--
指定页
IF
@coltype
=
1
IF
@orderby
=
0
SET
@id
=
''''''
ELSE
SET
@id
=
''''
+
CHAR(
255)
+
''''
ELSE
IF
@coltype
=
2
IF
@orderby
=
0
SET
@id
=
'''
1753-1-1
'''
ELSE
SET
@id
=
'''
9999-12-31
'''
ELSE
IF
@orderby
=
0
SET
@id
=
'
-2147483648
'
ELSE
SET
@id
=
'
2147483647
'
SET
@i
=
0
--
为减少之后SELECT TOP
的数据量,此处每10000条循环一次,以尽可能接近所查询页
WHILE
@i
<
@pagesize
*
@page
BEGIN
IF
@i
+
10000
<
@pagesize
*
@page
BEGIN
IF
@orderby
=
0
SET
@sql
=
'
SELECT @id=CASE
'
+
CAST(
@coltype
AS
varchar)
+
'
WHEN 1 THEN
''''''''
+CAST(MAX(
'
+
@col
+
'
) AS varchar(50))+
'
+
''''''''''
+
'
WHEN 2 THEN
''''''''
+CONVERT(char(23),MAX(
'
+
@col
+
'
),121)+
'
+
''''''''''
+
'
ELSE CAST(MAX(
'
+
@col
+
'
) AS varchar) END FROM (SELECT TOP 10000
'
+
@col
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
'
>
'
+
@id
+
'
ORDER BY
'
+
@col
+
'
) t
'
ELSE
SET
@sql
=
'
SELECT @id=CASE
'
+
CAST(
@coltype
AS
varchar)
+
'
WHEN 1 THEN
''''''''
+CAST(MIN(
'
+
@col
+
'
) AS varchar(50))+
'
+
''''''''''
+
'
WHEN 2 THEN
''''''''
+CONVERT(char(23),MIN(
'
+
@col
+
'
),121)+
'
+
''''''''''
+
'
ELSE CAST(MIN(
'
+
@col
+
'
) AS varchar) END FROM (SELECT TOP 10000
'
+
@col
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
'
<
'
+
@id
+
'
ORDER BY
'
+
@col
+
'
DESC) t
'
EXEC sp_executesql
@sql,N
'
@id varchar(50) OUTPUT
',
@id OUTPUT
SET
@i
=
@i
+
10000
IF
@i
+
10000
>=
@pagesize
*
@page
BREAK
END
ELSE
BREAK
END
--
上面的循环保证下面的子查询最多只有10000条数据
IF
@orderby
=
0
SET
@sql
=
'
SELECT TOP
'
+
CAST(
@pagesize
AS
varchar)
+
'
'
+
@collist
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
'
>
'
+
@id
+
'
AND
'
+
@col
+
'
NOT IN
'
+
'
(SELECT TOP
'
+
CAST(
@pagesize
*(
@page
-
1)
-
@i
AS
varchar)
+
'
'
+
@col
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
'
>
'
+
@id
+
'
ORDER BY
'
+
@col
+
'
) ORDER BY
'
+
@col
ELSE
SET
@sql
=
'
SELECT TOP
'
+
CAST(
@pagesize
AS
varchar)
+
'
'
+
@collist
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
'
<
'
+
@id
+
'
AND
'
+
@col
+
'
NOT IN
'
+
'
(SELECT TOP
'
+
CAST(
@pagesize
*(
@page
-
1)
-
@i
AS
varchar)
+
'
'
+
@col
+
'
FROM
'
+
@tb
+
@where1
+
@col
+
'
<
'
+
@id
+
'
ORDER BY
'
+
@col
+
'
DESC) ORDER BY
'
+
@col
+
'
DESC
'
EXEC(
@sql)
END
GO
--
测试
exec sp_page
'
person
',
'
id
',
0,
0,
'
*
',
5,
10,
8,
'',
'',
'
id != 77
' 对于比较简单的分页需求，一句sql搞定。
-- 第一个10表示pagesize,第二个10用pagesize*(pagenum-1)来算
-- 比如取第2页10 * (2-1) ,第3页 10 * (3 -1)
select
top
10 *
from person
where id
not
in (
select
top
10 id from person)
