Sqlserver 2008 通用分页储存过程

注意:下面Tbl为TBL

 代码如下:

create procedure spc_pageShow

(

@tableName    nvarchar(255),  ---表名

@primaryKeyField  nvarchar(50), --主键列名

@returnFileFieldLists  nvarchar(2000), --需要返回的列

@sortFieldLists  nvarchar(500),--需要排序的列(注意：该列在表中必须唯一,否则不能实现分页效果)

@pageSize int  =2,--每页显示的行数

@pageIndex  int =1,--当前页码

@orderType bit =0, --设置排序的类型(非0则降序)

@whereClause nvarchar(500)  ='' --查询条件(注意在下面的sql语句中不加where关键字)

)AS

BEGIN

   DECLARE @strSQL nvarchar(4000),--主句

                       @strSQLCount  nvarchar(1000),

                       @strTmp  nvarchar(200),--临时变量

                       @strOrder  nvarchar(500) ,--排序

                       @InnerOrder   nvarchar(600)

    Set  @whereClause=IsNull(@whereClause,‘’);

    Set  @InnerOrder=@SortFieldLists;

    ----返回总页数

   BEGIN

        IF (Len(LTrim(@whereClause))>0)

              Set @strSQLCount  ='select (CASE WHEN Count(*) % '+Cast(@pageSize as nvarchar(30)) +'>0

                                                                               THEN count(*) / ' + cast(@pageSize as nvarchar(30)) +' +1

                                                                               ELSE Count(*) / ' + cast(@pageSize as nvarchar(30)) + '

                                                                               END) AS TotalCount from '+@tableName +' ; ' ;

   END

   EXECUTE sp_ExecuteSQL @strSQLCount;

 

  BEGIN

       --设置排序字段

       /*

         IF (@orderType !=0)

                   Set @strOrder  ='  order by ' +@SortFieldLists + ' DESC';

          ELSE

                   Set @strSQL ='select Top ' + str(@pageSize) +' '  @parimaryKeyField  + ' ,' + @ReturnFieldLists +

                  ' From ' +@tableName + 'order by '+@Innerorder;

      */

     ----根据页码提取行数据(如果第一页就执行以上代码,这样会加快执行速度)

    IF (@pageIndex =1 )

    BEGIN

      IF (Len(@whereClause)  >0 )

        Set @strSQL =' Select Top ' + Str(@pageSize) + ' ' + @primaryKeyField + ' ,' + @ReturnFieldLists +

                                  'From ' + @tableName +'where ' + @whereClause + 'order by '+@InnerOrder;

      ELSE

          Set @strSQL ='Select TOP ' + str(@pageSize) + ' '  @primaryKeyField  + ' , '+ @ReturnFieldLists +

                                     'From '  + @tableName  + ' order by' +@InnerOrder;

   END

  ELSE

  BEGIN

      DECLARE   @Index  int,

                           @DotIndex   int,

                           @SortFieldTemp   nvarchar(2000),

                           @strTemp   nvarchar(200),

                           @strOrder1   nvarchar(2000)

      Set @strOrder1 = ''

      WHILE (1=1)

       BEGIN

              SELECT @Index =CHARINDEX (',' , @SortFieldLists)

              IF  @Index =0

               BEGIN

                     set @sortFieldTemp = @ SortFieldLists;

                     Select @DotIndex =CHARINDEX (' . ' , @SortFieldTemp)

                     IF @DotIndex >0

                          Set @strTemp =' tb1. ' + subString(@SortFieldTemp,@DotIndex +1 ,Len(@SortFieldTemp) - @DotIndex +1 )

                     ELSE

                         Set @strTemp ='tb1. ' +@SortFieldTemp

                     Set @strOrder1 =@strOrder1 +' , '+@strTemp

                     --退出循环

                     BREAK;

              END

             ELSE

             BEGIN

                 Set @sortFieldTemp = subString(@sortFieldLists, 1 ,@Index -1)

                 Select @DotIndex =CHARINDEX ('。' ,@sortFieldTemp)

                IF @DotIndex >0

                       Set @strTemp ='tb1. ' + subString(@SortFieldTemp,@DotIndex +1 , Len(@sortFieldTemp)-@DotIndex +1)

                ELSE

                      Set @strTemp ='tb. ' +@SortFieldTemp

               Set @strOrder1 = @strOrder1 + ', ' + @strTemp

               --截取字符串

              Select @SortFieldLists =subString (@sortFieldLists , @Index + 1, Len(@SortFieldLists) - @Index +1)

             END

       END

      ------截取第一个逗号

      Select @strOrder1= subString (@strOrder ,2 ,Len(@strOrder1))

       ---print @strOrder1

       --return

      /*

            Select @Index =CHARINDEX ('。' ,@sortFieldLists )

            Set @Index =IsNull(@Index, 0)

            IF @Index >0

                 Set @SortFieldLists =SubString (@SortFieldLists, @Index +1 ,Len(@SortFieldLists )-@Index +1)

            IF (@orderType !=0)

               Set @strOrder1 =' Order by Tb1. ' +@SortFieldLists + 'DESC ';

            ELSE

               Set @strOrder1 =' order by Tb1. ' +@SortFieldLists + 'ASC ';

      */

    IF (Len(@whereClaus) > 0)

          Set @strSQL =' Select TOP ' + Str(@pageSize) + ' Tb1. *

                                     From  (Select ' + @PrimaryKeyField + ' AS PrimaryKeyColumn, ' + @ReturnFieldLists +

                                                    ' From ' + @TableName +' where ' + @WhereClause +' ) AS Tbl

                                                      where NOT EXISTS (Select PrimaryKeyColumn

                                                                                                  From   (Select TOP ' + Str(@pageSize * (@pageIndex -1 ) ) + ' ' + @PrimarykeyField + ' AS PrimaryKeyColumn   From

                                                                                                              ' +  @TableName + '

                                                                                                                 where ' + @WhereClause + ' order By ' + @InnerOrder +

                                                                                                           ' ) AS B

                                                                                                             Where B.PrimaryKeyColumn =Tbl.PrimaryKeyColumn ) Order By '+@strOrder 1

      ELSE

         Set @strSQL = ' Select TOP ' + Str(@pageSize) + ' Tbl.*

                                         From (Select  ' + @PrimaryKeyField + ' AS PrimaryKeyColumn , ' + @ReturnFieldLists +

                                                                     ' From ' + @TableName +

                                                        ') AS Tbl

                                         Where NOT EXISTS (Select ' + @PrimaryKeyField + ' AS PrimaryKeyColumn

                                                              From ( Select  Top ' + Str(@pageSize * (@pageIndex -1 ) + ' ' + @PrimaryKeyField + ' AS PrimaryKeyColumn   From ' + @TableName + 'Order by ' + @InnerOrder +

                       ') AS B

                       Where B.PrimaryKeyColumn =Tb1.PrimaryKeyColumn ) Order by ' + @strOrder1

         END

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

  END

  ----print @strSQL;

 --动态执行SQL

  EXECUTE sp_ExecuteSQL @strSQL;

 END

GO