SQL Server 通用分页存储过程
Create Proc proc_SelectForPager ( @Tb_name varchar(max) ,--表名 @Order varchar(4000) ,--排序字段 @Offset int ,--跳过记录数 @Field varchar(400),--查询字段 @Limit int,--每页显示记录数 --@strWhere NVARCHAR(4000),--where 条件 @TotalCount int output ) As Declare @Exec_sql nvarchar(max) /* 设置where 条件 Set @strWhere=isnull(@strWhere,' where 1=1 ') set @strWhere=' where 1=1 and ' */ /* 查询总记录数*/ Set @Exec_sql='Set @TotalCount=(Select Count(1) From '+@Tb_name+' As a)' Exec sp_executesql @Exec_sql,N'@TotalCount int output',@TotalCount output /*设置排序字段*/ Set @Order=isnull(' Order by '+nullif(@Order,''),' Order By getdate()') /*分页查询*/ --Set @Exec_sql=' --;With CTE As --( -- Select *,row_number() Over('+@Order+') As r From (Select * from '+@Tb_name+') As a --) --Select '+@Field+' From CTE Where r Between (@CurrentPage-1)*@pagesize+1 And @CurrentPage*@pagesize Order By r --' --采用bootstrap 分页记录方式 Set @Exec_sql=' ;With CTE As ( Select *,row_number() Over('+@Order+') As r From (Select * from '+@Tb_name+') As a ) Select '+@Field+' From CTE Where r Between @Offset And @Limit Order By r ' Exec sp_executesql @Exec_sql,N'@Offset int,@Limit int',@Offset,@Limit
作者:逐帆
出处:http://www.cnblogs.com/langhua/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。