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