USE [QSM]GO/****** Object:  StoredProcedure [dbo].[UP_SysGetListByPage]    Script Date: 04/12/2017 17:47:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        
     
      -- Create date: 
      
       -- Description:    分页存储过程(只支技MSSQL2005及以上版本)-- =============================================ALTER PROCEDURE [dbo].[Sp_GetListByPage]    @Select nvarchar(1000)        --要查询的字段(已含SELECT)    ,@From nvarchar(1000)        --表名          (已含From)    ,@WhereSql nvarchar(1000)    --查询条件    (已含Where 1=1 )    ,@GroupBy nvarchar(250)        --分组        (已含Group By)    ,@Having nvarchar(250)        --过滤条件    (已含Having)    ,@OrderBy nvarchar(500)        --排序字段    (已含Order By)    ,@IsDesc bit=0                --排序：   0=ASC,1=DESC    ,@PageIndex int = 0            --页索引： 0=第1页    ,@PageSize int = 10            --页容量： 默认10行    ,@IsRecord bit =0            --0=数据集查询，1=取记录数ASBEGIN    SET NOCOUNT ON;        DECLARE @sqlFrom nvarchar(3000);    --要查询的字段及表    DECLARE @pagedSql nvarchar(max);    --拼接后的总SQL语句        SET @sqlFrom=N' from ' + @From;    IF(LEN(@WhereSql)>0)    SET @sqlFrom+=' where 1=1 ' + @WhereSql;    IF(LEN(@GroupBy)>0)        SET @sqlFrom+=' group by ' + @GroupBy;    IF(LEN(@Having)>0)        SET @sqlFrom+=' having ' + @Having;    IF(@IsDesc=1)            SET @OrderBy+=' DESC ';        SET @pagedSql=    N'with PagedList as '        +' ( '        +'    select top 100 percent '+@Select        +      ' , row_number() over (order by '+@OrderBy+') as mydata_RowNumber '        +         @sqlFrom         +' ) '        +' select * '        +' from PagedList '        +' where mydata_RowNumber between ' + STR(@PageIndex*@PageSize+1)         +                           ' and ' + STR(@PageIndex*@PageSize+@PageSize) ;        IF @IsRecord =1        SET @pagedSql = 'select count(1) as [RowCount] from ' + @From +' where 1=1 ' + @WhereSql ;        EXEC(@pagedSql)    --SELECT @pagedSql    --生成的语句END