之前用过的一些存储过程分页
大多数都只能根据主键排序,可是实际上,我们经常需要依据非主键排序并分页,比如,按工资大小
下面的代码能完成工作
ALTER
PROCEDURE
dbo.GetPageRecord
(
@tblName
varchar
(
255
),
--
表名
@fldName
varchar
(
255
),
--
排序字段
@KeyField
varchar
(
255
),
--
主键
@PageSize
int
=
10
,
--
页尺寸
@PageIndex
int
=
1
,
--
页码
@IsCount
bit
=
0
,
--
返回记录总数, 非 0 值则返回
@OrderType
bit
=
1
,
--
设置排序类型, 非 0 值则降序
@strWhere
varchar
(
4000
)
=
''
--
查询条件 (注意: 不要加 where)
)
AS
SET
NOCOUNT
ON
DECLARE
@PageLowerBound
int
DECLARE
@PageUpperBound
int
![None.gif](/Images/OutliningIndicators/None.gif)
--
Set the page bounds
SET
@PageLowerBound
=
@PageSize
*
@PageIndex
SET
@PageUpperBound
=
@PageLowerBound
+
@PageSize
+
1
![None.gif](/Images/OutliningIndicators/None.gif)
--
Create a temp table to store the select results
CREATE
TABLE
#tmp
(
RecNo
int
IDENTITY
(
1
,
1
)
NOT
NULL
,
oldid
int
)
--
generate record
Declare
@Sqlstr
varchar
(
6000
)
set
@sqlstr
=
'
select
'
+
@Keyfield
+
'
from
'
+
@tblname
if
(@strWhere
<>
''
)
begin
set
@Sqlstr
=
@sqlstr
+
'
where (
'
+
@strWhere
+
'
)
'
end
set
@sqlstr
=
@sqlstr
+
'
order by
'
+
@fldName
if
(@ordertype
=
0
)
begin
set
@sqlstr
=
@sqlstr
+
'
asc
'
end
else
begin
set
@sqlstr
=
@sqlstr
+
'
desc
'
end
set
@sqlstr
=
'
insert into #tmp (oldid)
'
+
@sqlstr
execute
(@sqlstr)
set
@sqlstr
=
'
SELECT TableA.* FROM
'
+
@tblname
+
'
TableA (nolock), #tmp T WHERE T.oldid = TableA.
'
+
@keyfield
+
'
AND T.RecNo >
'
+
cast
(@PageLowerBound
as
varchar
)
+
'
AND T.RecNo <
'
+
cast
(@PageUpperBound
as
varchar
)
+
'
ORDER BY T.RecNo
'
execute
(@sqlstr)