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

本文介绍了一个SQL Server存储过程，用于实现灵活的分页查询功能。该存储过程支持自定义表名、字段选择、排序方式及条件筛选等，通过动态SQL实现高效的数据检索。

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

create proc aaa
(
 @TableName nvarchar(2000),  
 @PageSize int = 0,
 @PageIndex int = 0,
 @ReturnFields nvarchar(2000),
 @OrderByfield nvarchar(2000),
 @Where nvarchar(2000),
 @OrderType int = 0,
 @TotalCount int output
)
as
begin
   declare @Start int
   declare @End int
   declare @CountSql nvarchar(2000)
   declare @Sql nvarchar(2000)
   declare @OrderBy nvarchar(2000)
   
   if @OrderType = 1
    begin
       set @OrderBy = 'order by '+REPLACE(@OrderByfield ,',',' asc,')+ ' asc'
    end
    else
    begin
       set @OrderBy  = ' order by '+ REPLACE(@OrderByfield,',',' desc,')+' desc'
    end
    
    set @Where = ' where 1=1 ' + @Where 
    set @CountSql = ' select @TotalCount= count(*) from ' +@TableName +' '+ @Where 
    
    execute sp_executesql @CountSql ,N'@TotalCount int output',@TotalCount output
    
    if @PageIndex is null or @PageIndex =''
    set @PageIndex = 1;
    if @PageSize is null or @PageSize =''
    set @PageSize =10
    
    set @Start = (@PageIndex-1) * @PageSize
    set @End = @PageIndex  * @PageSize
    
    set @Sql = 'select row_number() over ('+@OrderBy +')as row_num  from '+ @TableName +''+@Where 
    set @Sql ='select * from ('+@Sql+') as temp '+ @Where +'temp.row_num >='
    + CONVERT(nvarchar(200),@Start)+' temp.row_num <='+ CONVERT(nvarchar(200),@End)
    execute sp_executesql  @sql
    select @TotalCount
end

在实际应用中，出现数据查询不到情况时，可添加语句

this.anpDMBList.CurrentPageIndex = 1;

表明，将查询结果显示在第一页