转支持非主键排序的SQL存储过程

CREATE PROCEDURE pagination
    @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)

end
GO