肖高辉's blog

up everyday,just a little.

发一个我所经手的一个项目中的SQL 2005 分页通用存储过程，支持列排序

废话不说，直接帖代码。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        GOFI XIAO
-- Create date: 2007-07-28
-- Description:    通用分页存储过程增强版，增加对列的排序功能。
--使用注意：@sql语句中不加入 Order by 语句
-- =============================================
ALTER PROCEDURE [dbo].[spSplitPageEx]
(
    --组合后的查询语句
    @SQL varchar(8000),
    --排序字段
    @ColumnName Nvarchar(100),  
    --排序类型倒序或正序
    @OrderBy Nvarchar(10)='ASC', 
    --第几页
    @CurrentPage int=1,
    --页大小
    @PageSize int =10,
    --总页数
    @PageCount int =0 OUTPUT,
    --总记录数
    @TotalRecord int=0 OUTPUT


)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @ExceSQL nvarchar(4000)    
    --设置开始行号
    declare  @start_row_num AS int
    declare  @end_row_num AS int
    if(@CurrentPage=1)
      begin
        SET @start_row_num = (@CurrentPage - 1) * @PageSize
        SET @end_row_num = @start_row_num+@PageSize
      end
    else
      begin
        SET @start_row_num = ((@CurrentPage - 1) * @PageSize)+1
        SET @end_row_num = (@start_row_num+@PageSize)-1
      end
   
    --设置表示
    declare @RowNumber nvarchar(1000)
    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @ColumnName +' '+ @OrderBy + ') as RowNumber from '

    set @SQL = Replace(@SQL,'from',@RowNumber)

    --获得总记录数
    set @ExceSQL = 'WITH tmp AS (' + @SQL + ') 
        select @TotalRecord=max(RowNumber),@PageCount=CEILING((max(RowNumber)+0.0)/'+cast(@pageSize as varchar(50))+') from tmp'
    PRINT @ExceSQL
    execute sp_executesql @ExceSQL,N'@TotalRecord int output,@PageCount int output',@TotalRecord output,@PageCount OUTPUT

    --查询语句
    set @ExceSQL = 'WITH tmp AS (' + @SQL + ') 
        select * from tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
        + ' And ' + Convert(nvarchar,@end_row_num)

    execute(@ExceSQL)
PRINT @TotalRecord
PRINT @PageCount
    --防止为NULL fixed GOFI XIAO 2007-08-12
    IF @TotalRecord IS NULL 
        SET @TotalRecord=0
    IF @PageCount IS NULL 
        SET @PageCount=0
END




如何调用呢？下面给出一个示例：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:        GOFI XIAO
-- Create date: 2007-09-10
-- Description:    分页查询合同
--SelectContract.aspx?ContractCode=&ContractName=
-- =============================================
ALTER PROCEDURE [dbo].[spGetPagedList_Contract]
    -- Add the parameters for the stored procedure here
    (    
    @CurrentPage int=1,
    @PageSize int=10,    
    --按列排序
    @ColumnName varchar(50)='ContractName',
    --升序降序
    @OrderBy varchar(10)='DESC'    ,
    --查询条件
    @ContractCode varchar(50),
    @ContractName varchar(50)

)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
--组合查询语句
    declare @str varchar(8000)
set @str=N'SELECT 
c.*
 from Contract c 
WHERE 1=1 
'
--查询条件
IF @ContractCode<>''
    SET @str=@str+' AND c.ContractCode like ''%'+@ContractCode+'%'''
IF @ContractName<>''
    SET @str=@str+' AND c.ContractName like ''%'+@ContractName+'%'''

--*/
/*排序*/
declare @OrderColumnName varchar(50)
--设置一个默认的排序
set @OrderColumnName=' c.ContractName '
IF @ColumnName='ContractName'
    SET @OrderColumnName=' c.ContractName '
ELSE IF @ColumnName='ContractCode'
    SET @OrderColumnName=' c.ContractCode '



--return
declare @PageCount int
declare @TotalRecord int

exec spSplitPageEx @str,@OrderColumnName,@OrderBy,@CurrentPage,@PageSize,@PageCount OUTPUT,@TotalRecord OUTPUT
select @TotalRecord as TotalRecord,@PageCount as PageCount

END

前台页面如何调用呢？啊，这个还要问我？
我会在下篇文章中配合自定义分页控件来阐述。

posted on 2007-11-17 12:46 肖高辉(MSCD) 阅读(213) 评论(1)  编辑 收藏 网摘 所属分类: SQL Related

评论

#1楼 2008-07-25 13:00 fgfd[未注册用户]

di ng   回复  引用    




发表评论

昵称： [登录] [注册]

主页：

邮箱：(仅博主可见)

评论内容：

  登录  注册

[使用Ctrl+Enter键快速提交评论]

0 962414




相关文章:

相关链接: