简单的存储过程分页

最新推荐文章于 2024-09-08 09:10:51 发布

解.忧

最新推荐文章于 2024-09-08 09:10:51 发布

阅读量222

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/weixin_48519959/article/details/109740999

版权

本文介绍了如何使用SQL存储过程来实现高效的数据分页查询，详细解析了存储过程的编写步骤和关键代码，帮助读者理解如何在数据库层面优化分页操作。

摘要由CSDN通过智能技术生成

USE [Logistics]
GO
/****** Object:  StoredProcedure [dbo].[Proc_GetPagedUserList]    Script Date: 2020/9/11 9:38:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Proc_GetPagedUserList]
(
	@UserName nvarchar(32),
	@Phone nvarchar(16),
	@Sex int,
	@RoleId int,
	@AlterTimeStart datetime,
	@AlterTimeEnd datetime,

	@PageIndex INT,
	@PageSize INT,
	@TotalCount INT OUTPUT --定义为输出参数
)
AS
DECLARE @StrSql NVARCHAR(1024);--定义拼接SQL语句变量
DECLARE @strWhere NVARCHAR(1024)--定义where条件的变量
DECLARE @PageCount INT --定义总页数
SET @StrWhere=''


 if @UserName<> '' and @UserName is not null
 begin
	--拼接where条件
	set @strWhere=@strWhere+' and u.UserName='''+@UserName+''''
 end 

  if @Phone<> '' and @Phone is not null
 begin
	--拼接where条件
	set @strWhere=@strWhere+' and u.Phone='''+@Phone+''''
 end 

 
  if @Sex>-1
 begin
	--拼接where条件
	set @strWhere=@strWhere+' and u.Sex='+ CONVERT(varchar(2),@Sex) 
 end 

  if @RoleId>0
 begin
	--拼接where条件
	set @strWhere=@strWhere+' and r.RoleId='+ CAST(@RoleId as varchar(8) ) 
 end 

   if @AlterTimeStart<> '' and @AlterTimeStart is not null
 begin
	--拼接where条件
	set @strWhere=@strWhere+' and u.AlterTime>='''+ CONVERT(varchar(32),@AlterTimeStart)+''''
 end 

 
   if @AlterTimeEnd<> '' and @AlterTimeEnd is not null
 begin
	--拼接where条件
	set @strWhere=@strWhere+' and u.AlterTime<='''+ CONVERT(varchar(32),@AlterTimeEnd) +''''
 end 

SET @StrSql='SELECT @RowCount= COUNT(*) FROM [User] as u where 1=1 '+@strWhere

EXEC sp_executesql @StrSql,N'@RowCount INT OUTPUT',@TotalCount OUTPUT  --获取数据总记录数


IF (@PageIndex<1)
BEGIN
	SET @PageIndex=1;--如果当前页索引小于1，则显示首页数据
END 


SET @PageCount=CEILING(CONVERT(FLOAT,@TotalCount) /@PageSize)--获取总页数

IF(@PageIndex>@PageCount)
BEGIN
	SET @PageIndex=@PageCount
END

SET @StrSql='SELECT * FROM
(
select ROW_NUMBER() over(order by UserId) as RowNum, u.UserID,u.UserName,u.Account, u.Sex, u.Phone,u.Email, u.IsDelete,u.CheckInTime,u.AlterTime, r.RoleID,r.RoleName from [User] as u inner join [Role] as r on u.FK_RoleID=r.RoleID where 1=1 '+@strWhere+'
) AS temp WHERE temp.RowNum BETWEEN '+CONVERT(NVARCHAR(32),((@PageIndex-1)*@PageSize+1))+'  AND '+CONVERT(NVARCHAR(32),(@PageIndex*@PageSize)) 


EXEC sp_executesql @StrSql