SQL存储过程分页查询-优快云博客

本文介绍了一个SQL存储过程，用于实现数据库中的分页查询功能。该存储过程允许指定允许查询的字段、每页记录数、页码及额外条件。通过使用TOP关键字和子查询，实现了基于ID的分页效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE PROCEDURE [dbo].[Select_page_data_allow_fields_cond]
@allow_fields varchar(100) =" * ",
@DefRecordNum int =20,
@PageNo int =1,
@Where varchar(500) = null
AS
declare @R_end int
declare @S_sql varchar(500)
declare @condition varchar(500)
declare @condition2 varchar(500)

--if rtrim(@Where)!=""
--if rtrim(@Where)!=""

SET @r_end=(@PageNo-1)*@DefRecordNum+1

--正则页码
IF ( CAST(@r_end AS INT)<1)
SET @r_end = 1

SET @Where=rtrim(@Where)
--if not (@Where is null )
if (@Where <>'' )
SET @S_sql="SELECT TOP "+CAST(@DefRecordNum AS CHAR(4)) +space(2)+@allow_fields+" FROM BBSReply WHERE id>=(SELECT MAX(ID) AS r_begin FROM (SELECT TOP "+CAST(@R_end AS CHAR(5))+" ID FROM BBSReply WHERE "+@Where+" ORDER BY ID) airzen) AND "+@Where+" order by id"
ELSE
SET @S_sql="SELECT TOP "+CAST(@DefRecordNum AS CHAR(4)) +space(2)+@allow_fields+" FROM BBSReply WHERE id>=(SELECT MAX(ID) AS r_begin FROM (SELECT TOP "+CAST(@R_end AS CHAR(5))+" ID FROM BBSReply ORDER BY ID) airzen) order by id"

--PRINT @S_sql
EXEC(@S_sql)
GO

---------------------------------------------------

数据分页存储过程