sql存储过程分页排序问题
本帖最后由 wms102 于 2013-07-09 17:10:04 编辑 USE [database]
GO
/****** Object: StoredProcedure [dbo].[UpPagerSingle] Script Date: 07/09/2013 14:14:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[UpPagerSingle]
(
@TableName nvarchar(50), -- 表名
@ReturnFields nvarchar(2000) = '*', -- 需要返回的列
@PageSize int = 10, -- 每页记录数
@PageIndex int = 1, -- 当前页码
@Where nvarchar(2000) = '', -- 查询条件
@Orderfld nvarchar(2000), -- 排序字段名 最好为唯一主键
@OrderType int = 1 -- 排序类型 1:降序 其它为升序
)
As
Declare @TotalRecord int
Declare @TotalPage int
Declare @CurrentPageSize int
Declare @TotalRecordForPageIndex int
Declare @OrderBy nvarchar(255)
Declare @CutOrderBy nvarchar(255)
If(@PageIndex = 0)
SET @PageIndex = 1
If @OrderType = 1
Begin
Set @OrderBy = ' Order By ' + REPLACE(@Orderfld, ',', ' Desc,') + ' Desc '
Set @CutOrderBy = ' Order By '+ REPLACE(@Orderfld, ',', ' Asc,') + ' Asc '
End
Else
Begin
Set @OrderBy = ' Order By ' + REPLACE(@Orderfld, ',', ' Asc,') + ' Asc '
Set @CutOrderBy = ' Order By '+ REPLACE(@Orderfld, ',', ' Desc,') + ' Desc '
End
-- 记录总数
Declare @countSql nvarchar(4000)
Set @countSql = 'Select @TotalRecord = Count(*) From ' + @TableName + ' ' + @Where
Exec sp_executesql @countSql, N'@TotalRecord int out', @TotalRecord out
Set @TotalPage = (@TotalRecord - 1) / @PageSize + 1
Set @CurrentPageSize = @PageSize
If(@TotalPage = @PageIndex)
Begin
SET @CurrentPageSize = @TotalRecord % @PageSize
IF(@CurrentPageSize = 0)
SET @CurrentPageSize = @PageSize
End
-- 返回记录
Set @TotalRecordForPageIndex = @PageIndex * @PageSize
Exec('Select * From
(Select Top ' + @CurrentPageSize + ' * From
(Select Top ' + @TotalRecordForPageIndex + ' ' + @ReturnFields + '
From ' + @TableName + ' ' + @Where + ' ' + @OrderBy + ') TB2
' + @CutOrderBy + ') TB3
' + @OrderBy)
-- 返回总页数和总记录数
SELECT @TotalPage As PageCount, @TotalRecord As RecordCount
sql存储过程排序问题，如果记录有很多排序数字相同返回记录就有问题 SQL 存储过程 分页?排序
[解决办法]
sql存储过程排序问题，如果记录有很多排序数字相同返回记录就有问题
可以考虑排序函数变成不同的啊
[解决办法]
@orderby的最后加上“,id字段”
