CREATE PROCEDURE [dbo].[UP_CutPage]
@tbName nvarchar(255), -- 表名(允许多张表的join)
@keyorder nvarchar(255), -- 用于排序的字段名 后面可以追加' asc' 或' desc', 不支持对多个字段排序
@keymain nvarchar(255)='', -- 主键字段名(如果用于排序的字段的值是唯一的，应将此参数设为''或null(不这样将会降低效率); 如果用于排序的字段的值不是唯一的， 则需要写真实的主键) 后面可以追加' asc' 或' desc', 不支持多个主键
@columns nvarchar(1000)='*', -- 要选择的列 (注意: 本存储过程不会自动给列名或表名加方括号)
@strWhere nvarchar(3000) = '', -- 查询条件 (注意: 不要加 where)
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1 output, -- 页码
@RowsCount int=0 output, -- 记录总数,
@PageCount int=0 output --页面总数
AS
begin
declare @s1 nvarchar(100) -- 临时变量
declare @s2 nvarchar(100) -- 临时变量
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp nvarchar(3000) -- 临时变量
declare @strOrder nvarchar(2000) -- 排序类型
--取得总行数
if @strWhere != ''
set @strSQL='SELECT @RowsCount=count(*) FROM '+@tbName+' where ' + @strWhere
else
set @strSQL='SELECT @RowsCount=count(*) FROM '+@tbName
exec sp_executesql @strSQL, N'@RowsCount int out', @RowsCount out
set @strSQL=''
--计算总页数
set @PageCount=@RowsCount/@PageSize
if (@RowsCount % @PageSize<>0)
set @PageCount=@PageCount+1
if (@PageCount<1)
set @PageCount=1
--错误处理
if (@PageSize<1)
set @PageSize=1
if (@PageIndex<1)
set @PageIndex=1
if (@PageIndex>@PageCount)
set @PageIndex=@PageCount
--拼接Order By子句
declare @strOrderType1 nvarchar(50)
declare @strOrderType2 nvarchar(50)
if(CHARINDEX(' desc', @keyorder)>0)
begin
set @strOrderType1='desc'
set @keyorder=REPLACE(@keyorder, ' desc', '')
end
else
begin
set @strOrderType1='asc'
set @keyorder=REPLACE(@keyorder, ' asc', '')
end
----求字段类型start
declare @OrderTable nvarchar(255)
declare @OrderName nvarchar(255)
declare @OrderType varchar(255)
declare @OrderPrec varchar(50)
declare @OrderDot int
set @OrderDot=CHARINDEX('.', @keyorder)
IF @OrderDot > 0
BEGIN
SET @OrderTable = SUBSTRING(@keyorder, 0, @OrderDot)
SET @OrderName = SUBSTRING(@keyorder, @OrderDot + 1, LEN(@keyorder))
END
ELSE
BEGIN
SET @OrderTable = @tbName
SET @OrderName = @keyorder
END
set @s1=REPLACE(REPLACE (@OrderTable,'[',''),']','')
set @s2=REPLACE(REPLACE (@OrderName,'[',''),']','')
SELECT @OrderType=t.[name], @OrderPrec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @s1 AND c.[name] = @s2
IF CHARINDEX('char', @OrderType) > 0
SET @OrderType = @OrderType + '(' + CAST(@OrderPrec AS varchar) + ')'
----求字段类型end
if @strOrderType1='asc'
begin
set @strOrderType1 = '>'
set @strOrder = ' order by ' + @keyorder +' asc'
end
else
begin
set @strOrderType1 = '<'
set @strOrder = ' order by ' + @keyorder +' desc'
end
if @keymain is null
set @keymain=''
if @keymain<>''
begin
if(CHARINDEX(' desc', @keymain)>0)
begin
set @strOrderType2='desc'
set @keymain=REPLACE(@keymain, ' desc', '')
end
else
begin
set @strOrderType2='asc'
set @keymain=REPLACE(@keymain, ' asc', '')
end
----求字段类型start
declare @MainTable nvarchar(255)
declare @MainName nvarchar(255)
declare @MainType varchar(255)
declare @MainPrec varchar(50)
declare @MainDot int
set @MainDot=CHARINDEX('.', @keymain)
IF @MainDot > 0
BEGIN
SET @MainTable = SUBSTRING(@keymain, 0, @MainDot)
SET @MainName = SUBSTRING(@keymain, @MainDot + 1, LEN(@keymain))
END
ELSE
BEGIN
SET @MainTable = @tbName
SET @MainName = @keymain
END
set @s1=REPLACE(REPLACE (@MainTable,'[',''),']','')
set @s2=REPLACE(REPLACE (@MainName,'[',''),']','')
SELECT @MainType=t.name, @MainPrec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @s1 AND c.name = @s2
IF CHARINDEX('char', @MainType) > 0
SET @MainType = @MainType + '(' + CAST(@MainPrec AS varchar) + ')'
----求字段类型end
if @strOrderType2='asc'
begin
set @strOrderType2='>'
set @strOrder =@strOrder + ', ' + @keymain +' asc'
end
else
begin
set @strOrderType2='<'
set @strOrder =@strOrder + ', ' + @keymain +' desc'
end
end
--拼接查询语句
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + convert(nvarchar(100),@PageSize) + ' '+@columns+' from '
+ @tbName + ' ' + @strTmp + ' ' + @strOrder
end
else
begin
--求临界值
set @strSQL='declare @midData '+@OrderType+';'
if @MainType is not null
set @strSQL=@strSQL+'declare @midID '+@MainType+';'
set @strSQL=@strSQL+'SET ROWCOUNT '+ convert(nvarchar(100),(@PageIndex-1)*@PageSize)+';'
if @keymain<>''
set @strSQL =@strSQL+ 'select @midData= '
+ @keyorder + ', @midID=' + @keymain + ' from ' + @tbName
else
set @strSQL =@strSQL+ 'select @midData= '
+ @keyorder + ' from ' + @tbName
if @strWhere != ''
set @strSQL=@strSQL+ ' where ' + @strWhere
set @strSQL=@strSQL+' '+@strOrder+';'
--print (@strSQL); return
--最终查询语句
set @strSQL=@strSQL+'SET ROWCOUNT '+ convert(nvarchar(100),@PageSize) +';'
if @keymain<>''
set @strSQL =@strSQL+ 'select '+@columns+' from '
+ @tbName + ' where (' + @keyorder + @strOrderType1 + '@midData or ('
+ @keyorder + '=@midData and ' + @keymain + @strOrderType2 +'@midID))'
else
set @strSQL =@strSQL+ 'select '+@columns+' from '
+ @tbName + ' where ' + @keyorder + @strOrderType1 + '@midData'
if @strWhere <> ''
set @strSQL=@strSQL+' and '+ @strWhere
set @strSQL=@strSQL+' ' + @strOrder
end
--执行查询语句
--print (@strSQL); return
exec (@strSQL)
/*
------------调用示例-------------------
DECLARE @tbName nvarchar(255) -- 表名(允许多张表的join)
DECLARE @keyorder nvarchar(255) -- 用于排序的字段名 后面可以追加' asc' 或' desc', 不支持对多个字段排序
DECLARE @keymain nvarchar(255) -- 主键字段名(如果用于排序的字段的值是唯一的，应将此参数设为''或null(不这样将会降低效率); 如果用于排序的字段的值不是唯一的， 则需要写真实的主键) 后面可以追加' asc' 或' desc', 不支持多个主键
DECLARE @columns nvarchar(1000) -- 要选择的列 (注意: 本存储过程不会自动给列名或表名加方括号)
DECLARE @strWhere nvarchar(3000) -- 查询条件 (注意: 不要加 where)
DECLARE @PageSize int -- 页尺寸
DECLARE @PageIndex int -- 页码
DECLARE @RowsCount int -- 记录总数,
DECLARE @PageCount int -- 页面总数
select
@tbName='tab1 inner join tab2 on tab1.id=tab2.id',
@keyorder='tab1.id',
@keymain ='tab2.id asc',
@columns ='tab1.*,tab2.note',
@strWhere= 'tab2.id % 2 = 1 and tab1.id>6',
@PageSize = 5,
@PageIndex = 2,
@RowsCount =1 ,
@PageCount =1
EXECUTE [dbo].[UP_CutPage]
@tbName
,@keyorder
,@keymain
,@columns
,@strWhere
,@PageSize
,@PageIndex OUTPUT
,@RowsCount OUTPUT
,@PageCount OUTPUT
*/
end
/*
在所有分页存储过程的代码里，我的这份代码估计是最长的，我为何执意要写这样的代码呢？理由其实只有两个
通常分页存储过程只能按主键排序，而我这个允许按非主键排序
通常分页存储过程不会OUTPUT参数@PageIndex和@PageCount，而我这个则是例外
这个分页存储过程的代码虽然很长，但在百万级记录数的测试中， 其执行速度微微快于一般的分页存储过程
这个分页存储过程并非完全由我所写，是参考了几个比较好的分页存储过程而写的，当然, 所有分页存储过程的基本思想都是差不多的...
*/