22,210
社区成员
发帖
与我相关
我的任务
分享
alter PROCEDURE PrcPager
-- 获得某一页的数据 --
@currPage int = 1, --当前页页码 (即Top currPage)
@showColumn nvarchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)
@tempTableColumn nvarchar(2000),
@tabName nvarchar(2000), --需要查看的表名 (即 from table_name)
@strWhere nvarchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字
@ascColumn nvarchar(1000) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '', --主键名称
@pageSize int = 20, --分页大小
@PageCount int OUTPUT, --总页数
@SumCount int output --记录总数
AS
BEGIN -- 存储过程开始
-- 该存储过程需要用到的几个变量
DECLARE @strSql nvarchar(4000) --该存储过程最后执行的语句
declare @MaxOrMin nvarchar(100) --取最大值或最小值的判断
DECLARE @strOrderType nvarchar(1000) --排序类型语句 (order by column asc或者order by column desc)
declare @sqlTemp nvarchar(1000) --临时使用的sql语句
--计算总记录数 计算一次 页面传回值的时候就不用计算了
if @SumCount=0
begin
if @strWhere != ''
begin
SET @sqlTemp=N'SELECT @SumCount=COUNT(*)'
+N' FROM '+@tabName
+N' where '+@strWhere
end
else
begin
SET @sqlTemp=N'SELECT @SumCount=COUNT(*)'
+N' FROM '+@tabName
end
EXEC sp_executesql @sqlTemp,N'@SumCount int OUTPUT',@SumCount OUTPUT
end
SET @PageCount=(@SumCount+@PageSize-1)/@PageSize
---------------------------------------------------------------------------------------------------------------------------------------------
-- bitOrderType=1即执行降序
BEGIN
IF @bitOrderType = 1
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
set @MaxOrMin =' <(select min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
set @MaxOrMin =' >(select max'
END
-----------------------------------------------------------------------------------------------------------------------------------------
if(@strWhere!='')
set @strWhere=' where '+@strWhere
if @currPage=0
set @currPage=1
IF @currPage = 1 -- 如果是第一页
BEGIN
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@tempTableColumn+' FROM '+@tabName+' '+@strWhere+' '+@strOrderType
END
ELSE -- 其他页 前一半的记录if @currPage<@PageCount/2
BEGIN
set @strSql=N'if object_id(''tempdb..#sel'')is not null drop table #sel
'
--exec sp_executeSql @strSql
set @strSql=@strSql+'select identity(int,1,1) as id,* into #sel from (select TOP 100 PERCENT '+@tempTableColumn+'
from '+@tabName+@strWhere + @strOrderType+') as newtab
'
-- exec sp_executeSql @strSql --创建临时表
--拼接查询结果代码
SET @strSql =@strSql+'select top '+str(@pageSize)+' '+@showColumn+'
from #sel where id'+@MaxOrMin+'(id) as id from (select top '+str((@currPage-1)*@pageSize)+' id
from #sel ) as T )'
END
END
EXEC sp_executesql @strSql
END -- 存储过程结束