Create PROCEDURE [dbo].[sp_PageList]--创建存储过程
(
@StationID nvarchar(32),--工位ID
@PageSize INT, --每页显示的项数
@PageIndex INT--当前页数
)
AS
BEGIN
--获取总行数，用count(*)
declare @Sqlselect nvarchar(max)
declare @RecordCount INT
set @Sqlselect='select @RowCount=count(*) from Station_'+@StationID
PRINT @Sqlselect
exec sys.sp_executesql @Sqlselect,N'@RowCount INT output',@RecordCount output;--将总页数赋给@RecordCount output
--查询总页数(CEILING()向上舍入)
DECLARE @PageCount int --声明
set @PageCount=CEILING(CONVERT(float,@RecordCount/@PageSize))--获取页数
--判断当查询的页数小于1时则查询首页,大于最大值则为最后一页
if(@PageIndex<1)
begin
set @PageIndex=1;
end
if(@PageIndex>@PageCount)
begin
set @PageIndex=@PageCount;
end
--查询分页数据(CONVERT)，防止序号出现断层
set @Sqlselect= 'select * from(select ROW_NUMBER() over(order by [Id]) as rowindex ,* From Station_'+ @StationID+') a where a.rowindex>'+convert(nvarchar(32),(@PageSize*(@PageIndex-1)))+' and a.rowindex<='+CONVERT(nvarchar(32),(@PageSize*@PageIndex))+''
exec sp_executesql @Sqlselect
Print @Sqlselect
END
GO
