set ANSI_NULLS
ON
set QUOTED_IDENTIFIER
ON
go
ALTER
PROC
[
dbo
].
[
PROCE_PageView2000
]
(
@tbname
nvarchar(
100),
--
要分页显示的表名
@FieldKey
nvarchar(
1000),
--
用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent
int
=
1,
--
要显示的页码
@PageSize
int
=
10,
--
每页的大小(记录数)
@FieldShow
nvarchar(
1000)
=
'',
--
以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder
nvarchar(
1000)
=
'',
--
以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@WhereString
nvarchar(
1000)
=N
'',
--
查询条件
@RecordCount
int OUTPUT
--
总记录数
)
AS
SET NOCOUNT
ON
--
检查对象是否有效
--
IF OBJECT_ID(@tbname) IS NULL
--
BEGIN
--
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
--
RETURN
--
END
--
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
--
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
--
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
--
BEGIN
--
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
--
RETURN
--
END
--
分页字段检查
IF
ISNULL(
@FieldKey,N
'')
=
''
BEGIN
RAISERROR(N
'
分页处理需要主键(或者惟一键)
',
1,
16)
RETURN
END
--
其他参数检查及规范
IF
ISNULL(
@PageCurrent,
0)
<
1
SET
@PageCurrent
=
1
IF
ISNULL(
@PageSize,
0)
<
1
SET
@PageSize
=
10
IF
ISNULL(
@FieldShow,N
'')
=N
''
SET
@FieldShow
=N
'
*
'
IF
ISNULL(
@FieldOrder,N
'')
=N
''
SET
@FieldOrder
=N
''
ELSE
SET
@FieldOrder
=N
'
ORDER BY
'
+
LTRIM(
@FieldOrder)
IF
ISNULL(
@WhereString,N
'')
=N
''
SET
@WhereString
=N
''
ELSE
SET
@WhereString
=N
'
WHERE (
'
+
@WhereString
+N
'
)
'
--
如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值)
IF
@RecordCount
IS
NULL
BEGIN
DECLARE
@sql
nvarchar(
4000)
SET
@sql
=N
'
SELECT @RecordCount=COUNT(*)
'
+N
'
FROM
'
+
@tbname
+N
'
'
+
@WhereString
EXEC sp_executesql
@sql,N
'
@RecordCount int OUTPUT
',
@RecordCount OUTPUT
END
--
计算分页显示的TOPN值
DECLARE
@TopN
varchar(
20),
@TopN1
varchar(
20)
SELECT
@TopN
=
@PageSize,
@TopN1
=(
@PageCurrent
-
1)
*
@PageSize
--
第一页直接显示
IF
@PageCurrent
=
1
EXEC(N
'
SELECT TOP
'
+
@TopN
+N
'
'
+
@FieldShow
+N
'
FROM
'
+
@tbname
+N
'
'
+
@WhereString
+N
'
'
+
@FieldOrder)
ELSE
BEGIN
--
处理别名
IF
@FieldShow
=N
'
*
'
SET
@FieldShow
=N
'
a.*
'
--
生成主键(惟一键)处理条件
DECLARE
@Where1
nvarchar(
4000),
@Where2
nvarchar(
4000),
@s
nvarchar(
1000),
@Field sysname
SELECT
@Where1
=N
'',
@Where2
=N
'',
@s
=
@FieldKey
WHILE
CHARINDEX(N
'
,
',
@s)
>
0
SELECT
@Field
=LEFT(
@s,
CHARINDEX(N
'
,
',
@s)
-
1),
@s
=
STUFF(
@s,
1,
CHARINDEX(N
'
,
',
@s),N
''),
@Where1
=
@Where1
+N
'
AND a.
'
+
@Field
+N
'
=b.
'
+
@Field,
@Where2
=
@Where2
+N
'
AND b.
'
+
@Field
+N
'
IS NULL
',
@WhereString
=
REPLACE(
@WhereString,
@Field,N
'
a.
'
+
@Field),
@FieldOrder
=
REPLACE(
@FieldOrder,
@Field,N
'
a.
'
+
@Field),
@FieldShow
=
REPLACE(
@FieldShow,
@Field,N
'
a.
'
+
@Field)
SELECT
@WhereString
=
REPLACE(
@WhereString,
@s,N
'
a.
'
+
@s),
@FieldOrder
=
REPLACE(
@FieldOrder,
@s,N
'
a.
'
+
@s),
@FieldShow
=
REPLACE(
@FieldShow,
@s,N
'
a.
'
+
@s),
@Where1
=
STUFF(
@Where1
+N
'
AND a.
'
+
@s
+N
'
=b.
'
+
@s,
1,
5,N
''),
@Where2
=
CASE
WHEN
@WhereString
=
''
THEN N
'
WHERE (
'
ELSE
@WhereString
+N
'
AND (
'
END
+N
'
b.
'
+
@s
+N
'
IS NULL
'
+
@Where2
+N
'
)
'
--
执行查询
EXEC(N
'
SELECT TOP
'
+
@TopN
+N
'
'
+
@FieldShow
+N
'
FROM
'
+
@tbname
+N
'
a LEFT JOIN(SELECT TOP
'
+
@TopN1
+N
'
'
+
@FieldKey
+N
'
FROM
'
+
@tbname
+N
'
a
'
+
@WhereString
+N
'
'
+
@FieldOrder
+N
'
)b ON
'
+
@Where1
+N
'
'
+
@Where2
+N
'
'
+
@FieldOrder)
END
set ANSI_NULLS
ON
set QUOTED_IDENTIFIER
ON
go
CREATE
PROCEDURE
[
dbo
].
[
p_GetListByPage
]
(
@TableName
varchar(
50),
--
表名
@ReFieldsStr
varchar(
200)
=
'
*
',
--
字段名(全部字段为*)
@OrderString
varchar(
200),
--
排序字段(必须!支持多字段不用加order by)
@WhereString
varchar(
500)
=N
'',
--
条件语句(不用加where)
@PageSize
int
=
10,
--
每页多少条记录
@PageIndex
int
=
1 ,
--
指定当前为第几页
@TotalPage
int
=
0 output,
--
总页数
@TotalRecord
int
=
0 output
--
返回总记录数
)
AS
BEGIN
--
处理开始点和结束点
Declare
@StartRecord
int;
Declare
@EndRecord
int;
Declare
@TotalCountSql
nvarchar(
500);
Declare
@SqlString
nvarchar(
2000);
SET
@StartRecord
= (
@PageIndex
-
1)
*
@PageSize
+
1
SET
@EndRecord
=
@StartRecord
+
@PageSize
-
1
SET
@TotalCountSql
= N
'
select @TotalRecord = count(*) from
'
+
@TableName;
--
总记录数语句
SET
@SqlString
= N
'
(select row_number() over (order by
'
+
@OrderString
+
'
) as rowId,
'
+
@ReFieldsStr
+
'
from
'
+
@TableName;
--
查询语句
--
IF (
@WhereString!
=
''
or
@WhereString
!=
null)
BEGIN
SET
@TotalCountSql
=
@TotalCountSql
+
'
where
'
+
@WhereString;
SET
@SqlString
=
@SqlString
+
'
where
'
+
@WhereString;
END
--
第一次执行得到
IF(
@TotalRecord
is
null)
BEGIN
EXEC sp_executesql
@totalCountSql,N
'
@TotalRecord int out
',
@TotalRecord output;
--
返回总记录数
END
SET
@TotalPage
=
CEILING(
@TotalRecord
*
1.0
/
@PageSize)
--
--执行主语句
SET
@SqlString
=
'
select * from
'
+
@SqlString
+
'
) as t where rowId between
'
+
ltrim(
str(
@StartRecord))
+
'
and
'
+
ltrim(
str(
@EndRecord));
Exec (
@SqlString);
END
GO