SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_NULLS
ON
GO
/**/
/*
名 称: pr_Common_PagedSelect功 能: 获取指定页的数据创建标识：2008-05-12 15:30修改标识：修改原因：
*/
CREATE
PROCEDURE
[
dbo
]
.pr_Common_PagedSelect
@TableName
varchar
(
255
),
--
表名
@OrderField
varchar
(
255
),
--
排序字段名
@SelectedFields
varchar
(
255
),
--
选择字段名
@PageSize
int
,
--
页尺寸
@PageIndex
int
,
--
页码
@OrderType
bit
,
--
设置排序类型, 非 0 值则降序
@Condition
varchar
(
2000
)
--
查询条件 (注意: 不要加 where)
AS
declare
@strSQL
varchar
(
6000
)
--
主语句
declare
@strTmp
varchar
(
1000
)
--
临时变量
declare
@strOrder
varchar
(
500
)
--
排序类型
if
@OrderType
!=
0
begin
set
@strTmp
=
'
<(select min
'
set
@strOrder
=
'
order by [
'
+
@OrderField
+
'
] desc
'
end
else
begin
set
@strTmp
=
'
>(select max
'
set
@strOrder
=
'
order by [
'
+
@OrderField
+
'
] asc
'
end
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@SelectedFields
+
'
from [
'
+
@TableName
+
'
] where [
'
+
@OrderField
+
'
]
'
+
@strTmp
+
'
([
'
+
@OrderField
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@OrderField
+
'
] from [
'
+
@TableName
+
'
]
'
+
@strOrder
+
'
) as tblTmp)
'
+
@strOrder
if
@Condition
!=
''
Begin
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@SelectedFields
+
'
from [
'
+
@TableName
+
'
] where [
'
+
@OrderField
+
'
]
'
+
@strTmp
+
'
([
'
+
@OrderField
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@OrderField
+
'
] from [
'
+
@TableName
+
'
] where
'
+
@Condition
+
'
'
+
@strOrder
+
'
) as tblTmp) and
'
+
@Condition
+
'
'
+
@strOrder
End
if
@PageIndex
=
1
begin
set
@strTmp
=
''
if
@Condition
!=
''
set
@strTmp
=
'
where (
'
+
@Condition
+
'
)
'
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@SelectedFields
+
'
from [
'
+
@TableName
+
'
]
'
+
@strTmp
+
'
'
+
@strOrder
end
exec
(
@strSQL
)
--
获取记录集
GO
SET
QUOTED_IDENTIFIER
OFF
GO
SET
ANSI_NULLS
ON
GO