[分享加散分]sql server 2005分页存储过程和sql server 2000分页存储过程

博客专家认证

2008-11-06 10:04:52

sql server 2005分页存储过程和sql server 2000分页存储过程，sql 2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持jion的，sql2000的分页存储过程，也可以运行在sql2005上，但是性能没有sql2005的版本好。
sql 2005：



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE proc [dbo].[up_Page2005] 

 @TableName varchar(50),        --表名

 @Fields varchar(5000) = '*',    --字段名(全部字段为*)

 @OrderField varchar(5000),        --排序字段(必须!支持多字段)

 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)

 @pageSize int,                    --每页多少条记录

 @pageIndex int = 1 ,            --指定当前为第几页

 @TotalPage int output            --返回总页数 

as

begin



    Begin Tran --开始事务



    Declare @sql nvarchar(4000);

    Declare @totalRecord int;    



    --计算总记录数

         

    if (@SqlWhere='' or @sqlWhere=NULL)

        set @sql = 'select @totalRecord = count(*) from ' + @TableName

    else

        set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere



    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数        

    

    --计算总页数

    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)



    if (@SqlWhere='' or @sqlWhere=NULL)

        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 

    else

        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere    

        



    --处理页数超出范围情况

    if @PageIndex<=0 

        Set @pageIndex = 1

    

    if @pageIndex>@TotalPage

        Set @pageIndex = @TotalPage



     --处理开始点和结束点

    Declare @StartRecord int

    Declare @EndRecord int

    

    set @StartRecord = (@pageIndex-1)*@PageSize + 1

    set @EndRecord = @StartRecord + @pageSize - 1



    --继续合成sql语句

    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)

     print @sql   

    Exec(@Sql)

    ---------------------------------------------------

    If @@Error <> 0

      Begin

        RollBack Tran

        Return -1

      End

     Else

      Begin

        Commit Tran

        Return @totalRecord ---返回记录总数

      End    

end

sql 2005：



/****** 对象:  StoredProcedure [dbo].[up_Page2005V2]    脚本日期: 05/21/2008 11:27:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[up_Page2005V2] 

    @TableName varchar(50),        --表名

 @Fields varchar(5000) = '*',    --字段名(全部字段为*)

 @OrderField varchar(5000),        --排序字段(必须!支持多字段)

 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)

 @pageSize int,                    --每页多少条记录

 @pageIndex int = 1 ,            --指定当前为第几页

 @totalRecord int = 0,

 @TotalPage int output            --返回总页数

AS

BEGIN

    

     Begin Tran --开始事务



    Declare @sql nvarchar(4000); 



    if @totalRecord<=0 begin

        --计算总记录数

             

        if (@SqlWhere='' or @sqlWhere=NULL)

            set @sql = 'select @totalRecord = count(*) from ' + @TableName

        else

            set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere



        EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       

    end



    --计算总页数

    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)



    if (@SqlWhere='' or @sqlWhere=NULL)

        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 

    else

        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere    

        



    --处理页数超出范围情况

    if @PageIndex<=0 

        Set @pageIndex = 1

    

    if @pageIndex>@TotalPage

        Set @pageIndex = @TotalPage



     --处理开始点和结束点

    Declare @StartRecord int

    Declare @EndRecord int

    

    set @StartRecord = (@pageIndex-1)*@PageSize + 1

    set @EndRecord = @StartRecord + @pageSize - 1



    --继续合成sql语句

    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)

     print @sql   

    Exec(@Sql)

    ---------------------------------------------------

    If @@Error <> 0

      Begin

        RollBack Tran

        Return -1

      End

     Else

      Begin

        Commit Tran

        Return @totalRecord ---返回记录总数

      End   

END





GO

...全文

5040 57 打赏收藏转发到动态举报

写回复

用AI写文章

57 条回复

切换为时间正序

请发表友善的回复…

发表回复

haorenhl007 2012-07-08

打赏
举报

多谢周公指点

Terrancy 2012-05-11

打赏
举报

其实我想说的是无图无真相，应该把实验的分页的结果都贴上来的，这样人家才能心服口服的。更重要的是结果才是王道。。

yannyleeqr 2011-12-27

打赏
举报

刚接触存储过程，不是太懂。不过学习

aboutrhino 2011-11-23

打赏
举报

sql 2000的有问题：
当Order by 中不是用ID排序，
就会有问题了。

小狼15454 2011-10-27

打赏
举报

学习吧可还是很乱能不能写个通用的

kakajay008 2011-10-07

打赏
举报

每天回帖即可获得10分可用分每天回帖即可获得10分可用分每天回帖即可获得10分可用分

bbbrid 2011-07-05

打赏
举报

居然不支持DISTINCT

bbbrid 2011-06-01

打赏
举报

第3个存储过程真是垃圾，使用过程中出现的问题太多了，使用复杂的where查询条件时，居然不可以使用分号，对SQL注入也没做任何处理，我最后不得不返回来 select count(Id), 再 select *,哎

sxhus 2011-05-30

打赏
举报

学习一下

bbbrid 2011-05-27

打赏
举报

UP，UP

bbbrid 2011-05-27

打赏
举报





public IList<ProductInfo> GetProductsByCategoryId(int CategoryId,ref PageInfo pgInfo) 

        {

            SqlParameter[] param = new SqlParameter[4];

            param[0] = new SqlParameter("@CategoryId", CategoryId);

            param[1] = new SqlParameter("@PageIndex", pgInfo.PageIndex);

            param[2] = new SqlParameter("@PageSize", pgInfo.PageSize);

            param[3] = new SqlParameter("@PageCount", pgInfo.PageCount);

            param[3].Direction = ParameterDirection.Output;

            string sqlCmd = "EXEC GetPage 'MyShop_Products','ProductId,ImgUrl,ProductName,MarketPrice,SalePrice','ProductId DESC',CategoryId=@CategoryId ,@PageSize,@PageIndex,@PageCount OUTPUT";

            IList<ProductInfo> ProductList = new List<ProductInfo>();

            DataTable Table = SQLHelper.ExecuteTable(CommandType.Text, sqlCmd, param);

            pgInfo.PageCount = Convert.ToInt32(param[3].Value);   //将总页数赋给PageInfo

            foreach (DataRow Row in Table.Rows) 

            {

                ProductInfo newPro = new ProductInfo();

                newPro.ProductId = Convert.ToInt32(Row[1]);

                newPro.ImgUrl = Convert.ToString(Row[2]);

                if (newPro.ImgUrl == "")

                {

                    newPro.ImgUrl = "/images/nopic.jpg";

                }

                newPro.ProductName = Convert.ToString(Row[3]);

                newPro.MarketPrice = Convert.ToDecimal(Row[4]);

                newPro.SalePrice = Convert.ToDecimal(Row[5]);

                ProductList.Add(newPro);

            }

            return ProductList;

        }

这是我的代码，运行时提示'@CategoryId' 附近有语法错误。该怎么解决呀？

bbbrid 2011-05-27

打赏
举报

up_Page2005V2_Join，
这个函数在C#怎么调用呢？
比如有一个Products表，里面有ProductId,ProductName,CategoryId
现在要按照CategoryId来查询里面所有符合条件的记录
要是不带WHERE查询条件的，可以用EXEC up_Page2005V2_Join 'Products','*','ProductId','',@PageSize,@PageIndex,@PageCount OUTPUT
但是现在查询要用到条件，使用
EXEC up_Page2005V2_Join 'Products','*','ProductId','CategoryId=@CategoryId',@PageSize,@PageIndex,@PageCount OUTPUT
却不能运行，提示'@CategoryId' 附近有语法错误。请问该如何解决？