sql分页存储

最新推荐文章于 2025-08-30 19:53:37 发布

原创最新推荐文章于 2025-08-30 19:53:37 发布 · 713 阅读

0 ·

CC 4.0 BY-SA版权

sql server 专栏收录该内容

77 篇文章

订阅专栏

本文介绍两种使用SQL Server存储过程实现分页查询的方法。一种是通过计算开始和结束行号直接进行分页查询，另一种是利用动态SQL实现带有条件筛选的分页。这两种方法能够有效地提高大数据量情况下的查询效率。

分页存储工程

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[Proc_Test]    Script Date: 2015/11/2 16:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_Test]  
    @PageIndex INT,--当前页数索引
	@PageSize INT--每页显示行数
    AS  
	DECLARE @Begin INT=0
	DECLARE @End INT=0
	SET @Begin=@PageIndex*@PageSize+1
	SET @End=@Begin+@PageSize-1
	SELECT T2.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY StudentId ASC) AS RowNumber,  
	T1.* FROM (  
	  SELECT * FROM [dbo].[StudentDemo]
	) T1 )T2 WHERE RowNumber BETWEEN @Begin AND @End

SELECT Count(1) AS TotalCount FROM [dbo].[StudentDemo]

调用

DECLARE @PageIndex INT,@PageSize INT
SET @PageIndex=1
SET @PageSize=5
EXEC [dbo].[Proc_Test] @PageIndex,@PageSize

通过PING SQL的方式实现分页

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[PaggingMyTest]    Script Date: 2017/4/1 22:08:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		CHAD.CAO
-- Create date: 2017/4/1
-- Description:	分页获取测试数据
-- =============================================
ALTER PROCEDURE [dbo].[PaggingMyTest]
	@PageIndex INT,--当前页数索引  
    @PageSize INT,--每页显示行数  
	@MyName NVARCHAR(50),
	@MyAddress NVARCHAR(200)
AS
BEGIN
	DECLARE @sql VARCHAR(MAX)
	SET @sql='SELECT * FROM MyTest WHERE 1=1'
	IF(@MyName<>'')
	BEGIN
		SET @sql=@sql+' AND MyName LIKE ''%'+@MyName+'%'''
	END
	IF(@MyAddress<>'')
	BEGIN
		SET @sql=@sql+' AND MyAddress LIKE ''%'+@MyAddress+'%'''
	END
	--获取行数--
	EXEC('SELECT COUNT(1) AS TotalCount FROM('+@sql+') AS tb')
	--获取结果--
	DECLARE @Begin INT=0  
    DECLARE @End INT=0  
    SET @Begin=@PageIndex*@PageSize+1  
    SET @End=@Begin+@PageSize-1  

	SET @sql='SELECT T2.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY MyID ASC) AS RowNumber,    
    T1.* FROM ('+@sql+') T1 )T2 WHERE RowNumber BETWEEN '+CAST(@Begin AS VARCHAR(20))+' AND '+CAST(@End AS VARCHAR(20))

	PRINT @sql 
	EXEC(@sql)
END

调用

USE [Test]
GO

--创建table--
CREATE TABLE MyTest
(
	MyID INT PRIMARY KEY IDENTITY(1,1),
	MyName NVARCHAR(50),
	MyAddress NVARCHAR(200)
)
--DROP TABLE MyTest
--添加数据--
DECLARE @Counter INT 
SET @Counter=1
WHILE(@Counter<=100)
BEGIN
	INSERT INTO MyTest(MyName,MyAddress)VALUES('张三'+CAST(@Counter AS VARCHAR(20)),'地址'+CAST(@Counter AS VARCHAR(20)))
	SET @Counter=@Counter+1
END
SELECT * FROM MyTest


EXEC [dbo].[PaggingMyTest] 0,10,'',''
EXEC [dbo].[PaggingMyTest] 1,10,'',''
EXEC [dbo].[PaggingMyTest] 0,10,'1',''
EXEC [dbo].[PaggingMyTest] 1,10,'1',''
EXEC [dbo].[PaggingMyTest] 1,10,'2',''