SQL Server实现分页存储过程

这是我们的创建的学生表

创建SQL 执行查询

 最后附上源码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<体育界的扛霸子>
-- Create date: <2022-03-24>
-- Description:	<学生信息查询>
-- =============================================
CREATE PROCEDURE GetStudentList
	 @page int
	,@limit int
	,@StudentId nvarchar(128) 
	,@Name nvarchar(128) 
	,@Sex char(10)
	,@total int output
AS
BEGIN
	SET @total=(select COUNT(*) from Students 
	             where (@StudentId is null or StudentId=@StudentId)
				 and (@Name is null or Name=@Name)
			     and (@Sex is null or Sex=@Sex)
	 )

	 select * from Students 
	             where (@StudentId is null or StudentId=@StudentId)
				 and (@Name is null or Name=@Name)
			     and (@Sex is null or Sex=@Sex)
				 order by Number desc 
				 OFFSET (@page -1)*@limit ROWS FETCH NEXT @limit ROWS ONLY
END
GO