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