sql 2000 分页存储过程

set  ANSI_NULLS  ON set  QUOTED_IDENTIFIER  ON go --  [SelectBase] 1,1,'( select j.*,u.P_Name from Job j left join Users u on j.UserID=u.ID) T','Flag=0'              alter   procedure   [ SelectBase ]              @PageIndex                 int ,               @PageSize                 int ,               @TableName      nvarchar ( 2000 ),               @Where          nvarchar ( 2000 ) = ''                as                Declare   @rowcount            int                Declare   @intStart            int                Declare   @intEnd           int                declare   @Column1   varchar ( 32 )  -- 第一列名称              Declare   @SQl   nvarchar ( 2000 ),  @WhereR   nvarchar ( 1000 ),  @OrderBy   nvarchar ( 1000 )               set   @rowcount = 0                set  nocount  on                if   @Where <> ''                begin                set   @Where = '  and  ' + @Where                end                if   CHARINDEX ( ' order by ' ,  @Where ) > 0                begin                set   @WhereR = substring ( @Where ,  1 ,  CHARINDEX ( ' order by ' , @Where ) - 1 )     -- 取得条件               set   @OrderBy = substring ( @Where ,  CHARINDEX ( ' order by ' , @Where ),  Len ( @Where ))     -- 取得排序方式(order by 字段 方式)               end                else                begin                set   @WhereR = @Where                set   @OrderBy = '  order by id asc '                end                set   @SQl = ' SELECT @rowcount=count(*) from  ' + cast ( @TableName   as   nvarchar ( 2000 )) + '  where 1=1  ' + @WhereR                             exec  sp_executeSql  @SQl ,N ' @rowcount int output ' , @rowcount  output               if   @PageIndex = 0   and   @PageSize = 0      -- 不进行分页,查询所有数据列表               begin                set   @SQl = ' SELECT * from  ' + cast ( @TableName   as   nvarchar ( 2000 )) + '  where 1=1  ' + @Where                end                else      -- 进行分页查询数据列表               begin                set   @intStart = ( @PageIndex - 1 ) * @PageSize + 1 ;               set   @intEnd = @intStart + @PageSize - 1                declare   @PKName   nvarchar ( 50 )              if ( len ( @TableName ) > 50 )              begin                  Set   @Column1 = ' ID '              end              else              begin              set   @Column1 = col_name ( object_id ( @tableName ), 1 )  -- 设置第一列名称              end              set   @SQl = ' Create table #tem(Row int identity(1,1) not null,joinRow int)  '                 set   @SQl = @SQl + ' insert #tem(joinRow) select  ' + @Column1 + '  from  ' + @TableName   + '  where 1=1  ' + @WhereR                set   @SQl = @SQl + '  select * from  ' + @TableName + '  right join #tem  on  ' + @Column1 + ' =#tem.joinRow  '                 set   @SQl = @SQl + '  where #tem.Row between  ' + cast ( @intStart   as   varchar ) + '  and  ' + cast ( @intEnd   as   varchar )               end                -- PRINT @SQl              exec  sp_executeSql  @SQl                return   @rowcount  

            set nocount off 

版权声明：本文原创发表于博客园，作者为路过秋天，原文链接：http://www.cnblogs.com/cyq1162/archive/2009/08/13/1545028.html

相关资源：sqlserver 支持多表联合查询分页存储过程