代码
--
----------------------------------
--
用途：分页存储过程(对有主键的表效率极高)
--
说明：
--
----------------------------------
CREATE
PROCEDURE
[
dbo
]
.
[
UP_GetRecordByPage
]
@tblName
varchar
(
255
),
--
表名
@fldName
varchar
(
255
),
--
主键字段名
@PageSize
int
=
10
,
--
页尺寸
@PageIndex
int
=
1
,
--
页码
@IsReCount
bit
=
0
,
--
返回记录总数, 非 0 值则返回
@OrderType
bit
=
0
,
--
设置排序类型, 非 0 值则降序
@strWhere
varchar
(
1000
)
=
''
,
--
查询条件 (注意: 不要加 where)
@fldOrder
varchar
(
255
)
=
''
AS
declare
@strSQL
varchar
(
6000
)
--
主语句
declare
@strTmp
varchar
(
100
)
--
临时变量(查询条件过长时可能会出错，可修改100为1000)
declare
@strOrder
varchar
(
400
)
--
排序类型
declare
@strSQL2
varchar
(
2000
)
--
用于执行带返回总记录数的语句
--
--------------------------20090903 update by cjp begin-----------------------------------------
if
@fldOrder
!=
''
begin
set
@fldName
=
@fldOrder
end
--
---------------------------20090903 update by cjp end-------------------------------------------
if
@OrderType
!=
0
begin
set
@strTmp
=
'
<(select min
'
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] desc
'
if
@fldOrder
!=
''
begin
set
@strOrder
=
'
order by [
'
+
@fldOrder
+
'
] desc
'
end
end
else
begin
set
@strTmp
=
'
>(select max
'
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] asc
'
if
@fldOrder
!=
''
begin
set
@strOrder
=
'
order by [
'
+
@fldOrder
+
'
] asc
'
end
end
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from [
'
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@fldName
+
'
] from [
'
+
@tblName
+
'
]
'
+
@strOrder
+
'
) as tblTmp)
'
+
@strOrder
if
@strWhere
!=
''
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from [
'
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@fldName
+
'
] from [
'
+
@tblName
+
'
] where
'
+
@strWhere
+
'
'
+
@strOrder
+
'
) as tblTmp) and
'
+
@strWhere
+
'
'
+
@strOrder
if
@PageIndex
=
1
begin
set
@strTmp
=
''
if
@strWhere
!=
''
set
@strTmp
=
'
where
'
+
@strWhere
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from [
'
+
@tblName
+
'
]
'
+
@strTmp
+
'
'
+
@strOrder
end
if
@IsReCount
!=
0
begin
set
@strSQL2
=
'
select count(*) as Total from [
'
+
@tblName
+
'
]
'
+
'
where
'
+
@strWhere
set
@strSQL
=
@strSQL
+
'
;
'
+
@strSQL2
;
end
print
@strSQL
exec
(
@strSQL
)
GO
--
----------------------------------
--
用途：支持任意排序的分页存储过程
--
说明：
--
----------------------------------
CREATE
PROCEDURE
[
dbo
]
.
[
UP_GetRecordByPageOrder
]
@tblName
varchar
(
255
),
--
表名
@fldName
varchar
(
255
),
--
显示字段名
@OrderfldName
varchar
(
255
),
--
排序字段名
@StatfldName
varchar
(
255
),
--
统计字段名
@PageSize
int
=
10
,
--
页尺寸
@PageIndex
int
=
1
,
--
页码
@IsReCount
bit
=
0
,
--
返回记录总数, 非 0 值则返回
@OrderType
bit
=
0
,
--
设置排序类型, 非 0 值则降序
@strWhere
varchar
(
1000
)
=
''
--
查询条件 (注意: 不要加 where)
AS
declare
@strSQL
varchar
(
6000
)
--
主语句
declare
@strTmp
varchar
(
100
)
--
临时变量(查询条件过长时可能会出错，可修改100为1000)
declare
@strOrder
varchar
(
400
)
--
排序类型
if
@OrderType
!=
0
begin
set
@strTmp
=
'
<(select min
'
set
@strOrder
=
'
order by [
'
+
@OrderfldName
+
'
] desc
'
end
else
begin
set
@strTmp
=
'
>(select max
'
set
@strOrder
=
'
order by [
'
+
@OrderfldName
+
'
] asc
'
end
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@fldName
+
'
from [
'
+
@tblName
+
'
] where [
'
+
@OrderfldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@OrderfldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@OrderfldName
+
'
] from [
'
+
@tblName
+
'
]
'
+
@strOrder
+
'
) as tblTmp)
'
+
@strOrder
if
@strWhere
!=
''
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@fldName
+
'
from [
'
+
@tblName
+
'
] where [
'
+
@OrderfldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@OrderfldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@OrderfldName
+
'
] from [
'
+
@tblName
+
'
] where
'
+
@strWhere
+
'
'
+
@strOrder
+
'
) as tblTmp) and
'
+
@strWhere
+
'
'
+
@strOrder
if
@PageIndex
=
1
begin
set
@strTmp
=
''
if
@strWhere
!=
''
set
@strTmp
=
'
where
'
+
@strWhere
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@fldName
+
'
from [
'
+
@tblName
+
'
]
'
+
@strTmp
+
'
'
+
@strOrder
end
if
@IsReCount
!=
0
set
@strSQL
=
@strSQL
+
'
select count(1) as Total from [
'
+
@tblName
+
'
]
'
if
@strWhere
!=
''
set
@strSQL
=
@strSQL
+
'
where
'
+
@strWhere
exec
(
@strSQL
)
GO
转载于:https://www.cnblogs.com/windthunder/archive/2009/12/25/1632185.html
相关资源：JAVA上百实例源码以及开源项目
