CREATE
PROCEDURE
sp_Paging
@tblName
varchar
(
255
) ,
--
表名
@strGetFields
varchar
(
1000
)
=
'
*
'
,
--
需要返回的列
@fldName
varchar
(
255
)
=
''
,
--
排序的字段名
@pageSize
int
=
10
,
--
页尺寸
@pageIndex
int
=
1
,
--
页码
@recordCount
int
=
0
output,
--
返回记录总数
@pageCount
int
=
0
output,
--
分页总数
@orderType
int
=
0
,
--
设置排序类型, 非 0 值则降序
@strWhere
varchar
(
1500
)
=
''
--
查询条件 (注意: 不要加 where)
AS
declare
@strSQL
nvarchar
(
3000
)
--
主语句
declare
@strTmp
varchar
(
110
)
--
临时变量
declare
@strOrder
varchar
(
400
)
--
排序类型
begin
if
@strWhere
!=
''
set
@strSQL
=
'
select @recordCount = count(*) from
'
+
@tblName
+
'
where
'
+
@strWhere
else
set
@strSQL
=
'
select @recordCount = count(*) from
'
+
@tblName
end
exec
sp_executesql
@strSQL
,N
'
@recordCount int out
'
,
@recordCount
out
if
@recordCount
%
@pageSize
=
0
set
@pageCount
=
@recordCount
/
@pageSize
else
set
@pageCount
=
@recordCount
/
@pageSize
+
1
--
以上代码的意思是计算出记录是总数和求出总共的页数
begin
if
@orderType
!=
0
begin
set
@strTmp
=
'
<(select min
'
set
@strOrder
=
'
order by
'
+
@fldName
+
'
desc
'
--
如果@orderType不是0,就执行降序,这句很重要!
end
else
begin
set
@strTmp
=
'
>(select max
'
set
@strOrder
=
'
order by
'
+
@fldName
+
'
asc
'
end
if
@pageIndex
=
1
begin
if
@strWhere
!=
''
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tblName
+
'
where
'
+
@strWhere
+
'
'
+
@strOrder
else
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tblName
+
'
'
+
@strOrder
--
如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
if
@strWhere
!=
''
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tblName
+
'
where
'
+
@fldName
+
''
+
@strTmp
+
'
(
'
+
substring
(
@fldName
,
charindex
(
'
.
'
,
@fldName
)
+
1
,
len
(
@fldName
))
+
'
) from (select top
'
+
str
((
@pageIndex
-
1
)
*
@pageSize
)
+
'
'
+
@fldName
+
'
from
'
+
@tblName
+
'
where
'
+
@strWhere
+
'
'
+
@strOrder
+
'
) as tblTmp) and
'
+
@strWhere
+
'
'
+
@strOrder
else
set
@strSQL
=
'
select top
'
+
str
(
@pageSize
)
+
'
'
+
@strGetFields
+
'
from
'
+
@tblName
+
'
where
'
+
@fldName
+
''
+
@strTmp
+
'
(
'
+
substring
(
@fldName
,
charindex
(
'
.
'
,
@fldName
)
+
1
,
len
(
@fldName
))
+
'
) from (select top
'
+
str
((
@pageIndex
-
1
)
*
@pageSize
)
+
'
'
+
@fldName
+
'
from
'
+
@tblName
+
''
+
@strOrder
+
'
) as tblTmp)
'
+
@strOrder
end
end
print
@strSQL
exec
sp_executesql
@strSQL
GO
分页存储过程
最新推荐文章于 2018-07-15 00:49:06 发布