SQL SERVER带输出参数的存储过程 多条件拼接 分页存储过

多条件拼接分页存储过程

USE [BookNet]
GO
/****** Object:  StoredProcedure [dbo].[LoadRecordListByPage]    Script Date: 2021/6/17 11:15:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[LoadRecordListByPage]
    -- Add the parameters for the stored procedure here
     @pageIndex int--当前页数
     ,@pageSize int--每页显示数
     ,@keyword nvarchar(200)=null--关键字筛选
     ,@customerSelect nvarchar(100)=null--客户名称筛选
     ,@timeselect datetime=null--时间段筛选
     ,@startTime datetime=null--起始时间筛选
     ,@endTime datetime=null--结束时间筛选
     ,@pageCount int output--总记录数 输出参数
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    set nocount on

     --定义的变量,一定要给初始值
    declare @sqlWhere nvarchar(max)='',@sqlPaged nvarchar(max)='',@sqlstr nvarchar(max)=''
            
    print @customerSelect
   --拼接sql
   --关键字
   if(@keyword!='')
   begin
    set @sqlWhere+=' and ( SourceIp like ''%'+@keyword+'%''  or AccessPage like ''%'+@keyword+'%'' or serialNumber like ''%'+@keyword+'%'' or EquipmentName like ''%'+@keyword+'%'' or EquipmentAddress like ''%'+@keyword+'%'' )'
   end
   --客户名称
    if(@customerSelect!='')
   begin
    set @sqlWhere+=' and  CustomerName ='''+@customerSelect+''''
   end
   --时间段
   if(@timeselect!='')
   begin
   set @sqlWhere+=' and  CreateTime  >='''+CONVERT(varchar(20), @timeselect,120)+''''
   end
   --开始时间
   if(@startTime!='')
   begin
    set @sqlWhere+=' and  CreateTime  >='''+CONVERT(varchar(20), @startTime,120)+''''
   end
   --结束时间
   if(@endTime!='')
   begin
    set @sqlWhere+=' and  CreateTime  <='''+CONVERT(varchar(20), @endTime,120)+''''
   end

   set @sqlPaged = 'select top '+cast(@pageSize as nvarchar(20))+'  Id,CreateTime,SouecrUrl,SourceIp,AccessPage,[Site],serialNumber,EquipmentName,CustomerName,EquipmentAddress  
    from (select row_number() over(order by CreateTime desc) as rownumber,* from [WHStieUrlRecord] where 1=1 '+ @sqlWhere+') temp_row
where rownumber>'+ cast(((@pageIndex-1)*@pageSize) as nvarchar(20))

print @sqlPaged
     exec sp_executesql @sqlPaged


--and (@customerSelect is NULL OR @customerSelect='' OR temp_row.CustomerName = @customerSelect)

--and (@startTime is NULL OR @startTime='' OR temp_row.startTime >=@startTime)
--and (@endTime is NULL OR @endTime='' OR temp_row.endTime >=@endTime)

set @sqlstr='select count(*) from [WHStieUrlRecord] where 1=1 ' + @sqlWhere
exec sp_executesql @sqlstr,N'@c int out',@pageCount out
set @pageCount=@pageCount

--set @pageCount= (select count(*) from [WHStieUrlRecord] where 1=1 and (@keyword='' or SourceIp like @keyword) + @sqlWhere)


END

调用存储过程

declare @pageCount int

  exec [LoadRecordListByPage]    1,20,null,null,null,null,null,@pageCount output

  print @pageCount

image.png

对应输出的sql语句

image.png

select top 20  Id,CreateTime,SouecrUrl,SourceIp,AccessPage,[Site],serialNumber,EquipmentName,CustomerName,EquipmentAddress  
    from (select row_number() over(order by CreateTime desc) as rownumber,* from [WHStieUrlRecord] where 1=1 ) temp_row
where rownumber>0
 

=================================分割线================================

附加功能：关键字筛选 多条件筛选按照空格分开

   --拆分关键字
    declare @SplitChar varchar(1) = ' ',
            @Account varchar(200),  --截取后的字符
            @SplitCharPos int       --记录截取位置
            set @SplitCharPos =0;   --初始截取位置为0
            select @SplitCharPos =CHARINDEX(@SplitChar,@keyword)  --第一次出现的位置

            if @SplitCharPos=0
            begin
            select @Account=@keyword 
            set @sqlWhere+=' and (  SourceIp like ''%'+@Account+'%''  or AccessPage like ''%'+@Account+'%'' or serialNumber like ''%'+@Account+'%'' or EquipmentName like ''%'+@Account+'%'' or EquipmentAddress like ''%'+@Account+'%'' or [site] like ''%'+@Account+'%'' )'
            end
            else 
             begin

            while @SplitCharPos>0
            begin
             select @SplitCharPos = CHARINDEX(@SplitChar,@keyword)
             if @SplitCharPos=0
             begin
                select @Account=@keyword
                set @sqlWhere+=' and ( SourceIp like ''%'+@Account+'%''  or  AccessPage like ''%'+@Account+'%'' or serialNumber like ''%'+@Account+'%'' or EquipmentName like ''%'+@Account+'%'' or EquipmentAddress like ''%'+@Account+'%'' or [site] like ''%'+@Account+'%'' )'
             end
             else
             begin
                select @Account=LEFT(@keyword,@SplitCharPos-1)
                select @keyword = RIGHT(@keyword,len(@keyword)-len(@Account) - 1)
                if(@Account!='')
                begin
                set @sqlWhere+=' and ( SourceIp like ''%'+@Account+'%''  or  AccessPage like ''%'+@Account+'%'' or serialNumber like ''%'+@Account+'%'' or EquipmentName like ''%'+@Account+'%'' or EquipmentAddress like ''%'+@Account+'%'' or [site] like ''%'+@Account+'%'' )'
                end
                
             end
             print @Account
             --print @keyword
             end
            end

关键字拆分对应输出sql语句

image.png

select top 20  Id,CreateTime,SouecrUrl,SourceIp,AccessPage,[Site],serialNumber,EquipmentName,CustomerName,EquipmentAddress  
    from (select row_number() over(order by CreateTime desc) as rownumber,* from [WHStieUrlRecord] where 1=1  and ( SourceIp like '%touch%'  or  AccessPage like '%touch%' or serialNumber like '%touch%' or EquipmentName like '%touch%' or EquipmentAddress like '%touch%' or [site] like '%touch%' ) and ( SourceIp like '%117%'  or  AccessPage like '%117%' or serialNumber like '%117%' or EquipmentName like '%117%' or EquipmentAddress like '%117%' or [site] like '%117%' )) temp_row
where rownumber>0