MSSQL通用分页存储过程

最新推荐文章于 2022-12-13 16:44:16 发布

转载最新推荐文章于 2022-12-13 16:44:16 发布 · 95 阅读

本文介绍了一个使用SQL Server存储过程实现动态分页查询的方法，包括动态SQL的构造和执行，适用于不同表结构和查询条件的场景。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER        PROCEDURE [dbo].[GetDataSet]
@TableList Varchar(200)='*',
@TableName Varchar(300),
@SelectWhere Varchar(1000)='',
@SelectOrderId Varchar(200),
@SelectOrder Varchar(200)='',
@intPageNo int=1, --页号
@intPageSize int=10 --每页显示数
as
begin
    set nocount on--关闭计数
    declare @RecordCount int  --总记录数(存储过程输出参数)
    declare @TmpSelect      NVarchar(2000)
    declare @Tmp     NVarchar(600)

    if @SelectWhere=''
        begin
            set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName
        end
    else
        begin
            set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName +' where '+@SelectWhere
        end
    execute sp_executesql  @TmpSelect,    --执行上面的sql语句
    N'@RecordCount int OUTPUT' ,  --执行输出数据的sql语句，output出总记录数
    @RecordCount  OUTPUT
    --if (@RecordCount = 0)    --如果没有，则返回零
               --return 0
    /*判断页数是否正确*/
      if (@intPageNo - 1) * @intPageSize > @RecordCount   --页号大于总页数，返回错误
        return (-1)
    --set nocount off--打开计数

    if @SelectWhere != ''
        begin
            set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' , '''+str(@RecordCount)+''' as Count from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' where 1=1 and '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrder
        end
    else
        begin
            set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' , '''+str(@RecordCount)+''' as Count from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
        end
    execute sp_executesql @TmpSelect
    return(@@rowcount)
end