sql server存储过程分页

Create PROCEDURE [dbo].[Table_GetList]    
 @WhereStr     nvarchar(256) = '',   -- 查询条件(注意: 不要加 WHERE)     
 @OrderStr    nvarchar(128) = '',   -- 设置排序      
 @PageIndex    int = 1,              -- 页尺寸      
 @PageSize     int = 0 ,             -- 页码      
 @RecordCount int output             -- 总的记录数      
AS      
      
DECLARE @TableName    nvarchar(32)       -- 表名      
DECLARE @PriKeyName   nvarchar(32)       -- 主键列或标示列      
DECLARE @FldNames     nvarchar(512)      -- 字段名,多个字段通过逗号分割      
      
DECLARE @StrByPage nvarchar(1024)        -- 分页查询语句      
DECLARE @StrNoPage nvarchar(512)      -- 不分页查询语句，获得所有符合条件的记录      
DECLARE @StrTotal nvarchar(512)          -- 统计符合条件的纪录数      
DECLARE @StrNonResult nvarchar(512)      -- 返回空记录的语句      
DECLARE @StrTmp nvarchar(128)      
DECLARE @StrOrder nvarchar(128)      
DECLARE @TotalPage int      
      
SET @TableName = 'ProductActions as b'      
SET @PriKeyName = 'ActionID'      
SET @FldNames = 'b.*'      
SET @TotalPage = 0       
      
--不传值默认主键降序      
IF @OrderStr is null or @OrderStr=''            
SET @OrderStr = '1'       
      
IF @OrderStr = '0' -- 升序      
BEGIN      
    SET @StrTmp = @PriKeyName + '>(SELECT max([' + @PriKeyName + ']) FROM '      
    SET @StrOrder = ' order by ' + @PriKeyName + ' asc'      
END      
ELSE IF @OrderStr = '1' -- 降序      
BEGIN      
    SET @StrTmp = @PriKeyName + '<(SELECT min([' + @PriKeyName + ']) FROM '      
    SET @StrOrder = ' order by ' + @PriKeyName + ' desc'      
END      
ELSE -- 用户自定义排序规则      
BEGIN      
    SET @StrTmp = ''      
    SET @StrOrder = ' order by ' + @OrderStr      
END      
SET @StrNonResult = 'SELECT ' + @FldNames + ' FROM ' + @TableName + ' WHERE 1=2'      
IF @WhereStr is null or @WhereStr = '' -- 如果没有额外的查询条件      
BEGIN      
 SET @StrTotal = N'SELECT @RecordCount = count(1) FROM ' + @TableName       
 SET @StrNoPage = N'SELECT ' + @FldNames + ' FROM ' + @TableName + @StrOrder      
END      
ELSE      
BEGIN      
 SET @StrTotal = N'SELECT @RecordCount = count(1) FROM ' + @TableName + ' WHERE ' + @WhereStr      
 SET @StrNoPage = N'SELECT ' + @FldNames + ' FROM ' + @TableName + ' WHERE ' + @WhereStr + @StrOrder      
END      
      
-- 取得所有符合查询条件的记录数      
EXEC sp_EXECuteSql @StrTotal,N'@RecordCount int output',@RecordCount output      
-- 如果没有适合条件的记录时,提供一个空的记录集并退出查询      
IF @RecordCount = 0      
BEGIN      
 SET @TotalPage = 0      
 EXEC sp_EXECuteSql @StrNonResult       
 RETURN 0      
END      
-- 执行查询，此时记录集不为空      
IF @PageSize = 0   -- 当不需要分页时      
BEGIN      
 SET NOCOUNT ON      
 SET @TotalPage = 0      
 EXEC sp_EXECuteSql @StrNoPage      
 RETURN      
END      
ELSE -- 当需要分页时      
BEGIN      
 SET NOCOUNT ON      
 -- 得到记录的页数，并调整页号，分页从1开始      
 SET @TotalPage=CEILING(cast(@RecordCount as float)/@PageSize)      
 IF(@PageIndex>@TotalPage)      
  SET @PageIndex=@TotalPage      
 IF(@PageIndex <1)      
  SET @PageIndex=1      
 IF @PageIndex =1 -- 如果是第一页      
 BEGIN      
  IF @WhereStr=''      
   SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames + ' FROM ' + @TableName + @StrOrder      
  ELSE      
   SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames + ' FROM ' + @TableName + ' WHERE ' + @WhereStr + @StrOrder      
 END      
 ELSE -- 以后页      
 BEGIN      
        
  IF (@OrderStr='0' or @OrderStr='1') -- 按主键升序或降序      
  BEGIN      
   IF @WhereStr=''      
    SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames       
     + ' FROM ' + @TableName       
     + ' WHERE ' + @StrTmp      
     + ' (SELECT TOP ' + cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @PriKeyName       
     + ' FROM ' + @TableName + @StrOrder + ') as tmptbl)'      
     + @StrOrder      
   ELSE      
    SET @StrByPage = N'SELECT TOP ' + cast(@PageSize as varchar) + ' ' + @FldNames       
     + ' FROM ' + @TableName       
     + ' WHERE ' + @StrTmp      
     + ' (SELECT TOP '+ cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @PriKeyName       
     + ' FROM ' + @TableName + ' WHERE ' + @WhereStr + @StrOrder + ') as tmptbl)'      
     + ' and ' + @WhereStr      
     + @StrOrder      
  END      
  ELSE  -- 没有排序规则或者用户自定义规则      
  BEGIN       
   IF @WhereStr=''        
    SET @StrByPage = N'WITH cte AS('        
    + ' SELECT ROW_NUMBER() OVER('+@StrOrder+') AS ROWNUMBER ,' + @FldNames         
    + ' FROM '+ @TableName         
    + ') SELECT * FROM cte WHERE ROWNUMBER BETWEEN '        
    + CAST(((@PageIndex -1) * @PageSize +1) AS varchar) + ' AND '         
    + CAST(@PageIndex * @PageSize AS varchar)       
   ELSE        
    SET @StrByPage = N'WITH cte AS('        
    + ' SELECT ROW_NUMBER() OVER('+@StrOrder+') AS ROWNUMBER ,' + @FldNames         
    + ' FROM '+ @TableName + ' WHERE ' + @WhereStr        
    +') SELECT * FROM cte WHERE ROWNUMBER BETWEEN '        
    + CAST(((@PageIndex -1) * @PageSize +1) AS varchar) + ' AND '         
    + CAST(@PageIndex * @PageSize AS varchar)        
  END      
 END      
      
END      
      
--PRINT @StrByPage      
EXEC sp_executeSql @StrByPage      
RETURN