--通用分页存储过程
Create
PROCEDURE
[dbo].[GetRecordFromPage]
@tblName
varchar
(255),
-- 表名
@fldName
varchar
(255)=
'AutoID'
,
--关键字段名
@PageSize
int
= 10,
-- 页尺寸
@PageIndex
int
= 1,
-- 页码
@IsCount
bit
= 0,
-- 返回记录总数, 非 0 值则返回
@OrderType
bit
= 0,
-- 设置排序类型, 非 0 值则降序
@strWhere
varchar
(1000) =
''
,
-- 查询条件 (注意: 不要加 where)
@RecordCount
int
output
--总记录数
AS
declare
@ss nvarchar(4000)
if @strWhere !=
''
set
@ss =
'select @RecordCount=count(*) from '
+ @tblName +
' where '
+ @strWhere
else
set
@ss =
'select @RecordCount=count(*) from '
+ @tblName
--print @ss
exec
sp_executesql @ss,N
'@RecordCount Int out'
,@RecordCount
OutPut
declare
@strSQL
varchar
(6000)
-- 主语句
declare
@strTmp
varchar
(500)
-- 临时变量
declare
@strOrder
varchar
(400)
-- 排序类型
declare
@strOrderw
varchar
(400)
-- 排序类型
-- 如果是查询记录总数,直接使用Count(0)函数
set
@strOrderw=
' order by ['
+ @fldName +
'] '
if @IsCount != 0
begin
if @strWhere !=
''
set
@strSQL =
'select count(*) as Total from '
+ @tblName +
' where '
+ @strWhere
else
set
@strSQL =
'select count(*) as Total from '
+ @tblName
end
--如果是想查询记录,则
else
begin
if @PageIndex = 1
begin
set
@strTmp =
''
--如果是降序查询……
if @OrderType != 0
begin
set
@strOrder =
' order by ['
+ @fldName +
'] desc'
end
--如果是升序查询……
else
begin
set
@strOrder =
' order by ['
+ @fldName +
'] asc'
end
if @strWhere !=
''
set
@strTmp =
' where '
+ @strWhere
set
@strSQL =
'select top '
+
cast
(@PageSize
as
varchar
)+
' * from '
+@tblName+@strTmp+
' '
+@strOrder
end
else
begin
declare
@topTmp
varchar
(100)
--如果是降序查询……
if @OrderType != 0
begin
set
@strTmp =
'>=(select min'
set
@strOrder =
' order by ['
+ @fldName +
'] desc'
set
@topTmp=
cast
((@PageIndex*@PageSize)
as
varchar
)
end
--如果是升序查询……
else
begin
set
@strTmp =
'>(select max'
set
@strOrder =
' order by ['
+ @fldName +
'] asc'
set
@topTmp=
cast
((@PageIndex*@PageSize-@PageSize)
as
varchar
)
end
if @strWhere !=
''
set
@strSQL =
'select top '
+ str(@PageSize) +
' * from '
+ @tblName +
' where ['
+ @fldName +
']'
+ @strTmp +
'(['
+ @fldName +
']) from (select top '
+@topTmp+
' ['
+ @fldName +
'] from '
+ @tblName +
' where '
+ @strWhere +
' '
+ @strOrder +
') as tblTmp) and '
+ @strWhere +
' '
+ @strOrderw
else
set
@strSQL =
'select top '
+ str(@PageSize) +
' * from '
+ @tblName +
' where ['
+ @fldName +
']'
+ @strTmp +
'(['
+ @fldName +
']) from (select top '
+@topTmp+
' ['
+ @fldName +
'] from '
+ @tblName + @strOrder +
') as tblTmp)'
+ @strOrderw
end
end
--print @strSQL
exec
(@strSQL)