SqlServer-存储过程分页
学习资料参考:https://blog.csdn.net/bodilove/article/details/73991783
https://www.cnblogs.com/JustYong/p/6078353.html
第一种
CREATE PROCEDURE [dbo].[OrderInfo_PAGE]
@TableName VARCHAR(50), --表名
@ReFieldsStr VARCHAR(200) = '*', --字段名(全部字段为*)
@OrderString VARCHAR(200), --排序字段(必须!支持多字段不用加order by)
@WhereString VARCHAR(500) =N'', --条件语句(不用加where)
@PageSize INT, --每页多少条记录
@PageIndex INT = 1 , --指定当前为第几页
@TotalRecord INT OUTPUT --返回总记录数
AS
BEGIN
--处理开始点和结束点
DECLARE @StartRecord INT;
DECLARE @EndRecord INT;
DECLARE @TotalCountSql NVARCHAR(500);
DECLARE @SqlString NVARCHAR(2000);
SET @StartRecord = (@PageIndex-1)*@PageSize + 1--起始记录
SET @EndRecord = @StartRecord + @PageSize - 1 --结尾记录
SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'[email protected]+' from '+ @TableName;--查询语句
--
IF (@WhereString! = '' or @WhereString!=null)
BEGIN
SET @[email protected] + ' where '+ @WhereString;
SET @SqlString [email protected]+ ' where '+ @WhereString;
END
--第一次执行得到
EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord OUTPUT;--返回总记录数
----执行主语句
SET @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
Exec(@SqlString)
END
declare @totalCount int
exec OrderInfo_PAGE 'OrderProduct','*','OrderGuid','',3,1,@totalCount output;
select @totalCount as totalCount;--总记录数。
declare @totalCount int
exec OrderInfo_PAGE 'OrderProduct','*','OrderGuid','',3,1,@totalCount output;
exec OrderInfo_PAGE 'OrderProduct','*','OrderGuid','',3,2,@totalCount output;
select @totalCount as totalCount;--总记录数。
第二种-使用临时表
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='TempPage')
DROP PROC TempPage
GO
CREATE PROCEDURE TempPage
@PageSize INT, --每页大小
@PapeIndex INT, --当前索引
@PageCount INT OUTPUT, --返回总页数
@RecordCount INT OUTPUT --返回总记录条数
AS
BEGIN
--先创建临时表存放表连接查询数据
SELECT * INTO #NewsTemp FROM
(
SELECT
OP.OrderGuid,
OP.ProductGuid,
OP.ProductName,
O.LoginId ,
ROW_NUMBER() OVER(ORDER BY OP.OrderGuid DESC) AS RowNumber
FROM OrderProduct AS OP
INNER JOIN OrderInfo AS O ON O.OrderGuid=OP.OrderGuid
) T
--从临时表中查询分页数据
SELECT * FROM #NewsTemp WHERE RowNumber BETWEEN @PageSize*(@PapeIndex-1)+1 AND (@PageSize*@PapeIndex)
--查询总记录条数
SELECT @RecordCount=COUNT(1) FROM #NewsTemp
--计算总页数
SET @PAGECOUNT = CEILING(@RecordCount*1.0/@PageSize)
END
declare @pagecount int --接收总页数
declare @recordcount int --接收总记录条数
exec TempPage 4,1,@pagecount output, @recordcount output
select @pagecount as [pagecount];
select @recordcount as recordcount;
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[paging_procedure]') AND OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROCEDURE [DBO].[paging_procedure]
GO
CREATE PROCEDURE paging_procedure
@PageSize INT, --每页大小
@PapeIndex INT, --当前索引
@PageCount INT OUTPUT, --返回总页数
@RecordCount INT OUTPUT --返回总记录条数
AS
BEGIN
SELECT TOP (SELECT @PageSize) * -- 这里注意一下,不能直接把变量放在这里,要用SELECT
FROM (SELECT ROW_NUMBER() OVER(ORDER BY OrderGuid) AS RowNumber,* FROM OrderProduct) TEMP_ROW
WHERE RowNumber>(@PapeIndex-1)*@PageSize;
--查询总记录条数
SELECT @RecordCount=COUNT(1) FROM OrderProduct
--计算总页数
SET @PAGECOUNT = CEILING(@RecordCount*1.0/@PageSize)
END
declare @pagecount int --接收总页数
declare @recordcount int --接收总记录条数
exec paging_procedure 4,1,@pagecount output, @recordcount output
select @pagecount as [pagecount];
select @recordcount as recordcount;