利用表变量实现分页

一、

 


  1. 代码  
  2.  
  3.  CREATE PROCEDURE [dbo].[GetRecordFromPage]   
  4.     @SelectList            VARCHAR(2000),    --欲选择字段列表  
  5.     @TableSource        VARCHAR(100),    --表名或视图表   
  6.     @SearchCondition    VARCHAR(2000),    --查询条件  
  7.     @OrderExpression    VARCHAR(1000),    --排序表达式  
  8.     @PageIndex            INT = 1,        --页号,从0开始  
  9.     @PageSize            INT = 10        --页尺寸  
  10. AS   
  11. BEGIN  
  12.     IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''  
  13.     BEGIN  
  14.         SET @SelectList = '*' 
  15.     END  
  16.     PRINT @SelectList  
  17.       
  18.     SET @SearchCondition = ISNULL(@SearchCondition,'')  
  19.     SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))  
  20.     IF @SearchCondition <> ''  
  21.     BEGIN  
  22.         IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'  
  23.         BEGIN  
  24.             SET @SearchCondition = 'WHERE ' + @SearchCondition  
  25.         END  
  26.     END  
  27.     PRINT @SearchCondition  
  28.  
  29.     SET @OrderExpression = ISNULL(@OrderExpression,'')  
  30.     SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))  
  31.     IF @OrderExpression <> ''  
  32.     BEGIN  
  33.         IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'  
  34.         BEGIN  
  35.             SET @OrderExpression = 'ORDER BY ' + @OrderExpression  
  36.         END  
  37.     END  
  38.     PRINT @OrderExpression  
  39.  
  40.     IF @PageIndex IS NULL OR @PageIndex < 1 
  41.     BEGIN  
  42.         SET @PageIndex = 1 
  43.     END  
  44.     PRINT @PageIndex  
  45.     IF @PageSize IS NULL OR @PageSize < 1 
  46.     BEGIN  
  47.         SET @PageSize = 10 
  48.     END  
  49.     PRINT  @PageSize  
  50.  
  51.     DECLARE @SqlQuery VARCHAR(4000)  
  52.  
  53.     SET @SqlQuery='SELECT '+@SelectList+',RowNumber   
  54.     FROM   
  55.         (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber   
  56.           FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource   
  57.     WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)   
  58.     + ' AND ' +   
  59.     CAST((@PageIndex * @PageSize) AS VARCHAR)   
  60.     PRINT @SqlQuery  
  61.     SET NOCOUNT ON  
  62.     EXECUTE(@SqlQuery)  
  63.     SET NOCOUNT OFF  
  64.    
  65.     RETURN @@RowCount  
  66. END 

 


  1. 代码  
  2.  
  3.  CREATE PROCEDURE [dbo].[GetRecordFromPage]   
  4.     @SelectList            VARCHAR(2000),    --欲选择字段列表  
  5.     @TableSource        VARCHAR(100),    --表名或视图表   
  6.     @SearchCondition    VARCHAR(2000),    --查询条件  
  7.     @OrderExpression    VARCHAR(1000),    --排序表达式  
  8.     @PageIndex            INT = 1,        --页号,从0开始  
  9.     @PageSize            INT = 10        --页尺寸  
  10. AS   
  11. BEGIN  
  12.     IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''  
  13.     BEGIN  
  14.         SET @SelectList = '*' 
  15.     END  
  16.     PRINT @SelectList  
  17.       
  18.     SET @SearchCondition = ISNULL(@SearchCondition,'')  
  19.     SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))  
  20.     IF @SearchCondition <> ''  
  21.     BEGIN  
  22.         IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'  
  23.         BEGIN  
  24.             SET @SearchCondition = 'WHERE ' + @SearchCondition  
  25.         END  
  26.     END  
  27.     PRINT @SearchCondition  
  28.  
  29.     SET @OrderExpression = ISNULL(@OrderExpression,'')  
  30.     SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))  
  31.     IF @OrderExpression <> ''  
  32.     BEGIN  
  33.         IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'  
  34.         BEGIN  
  35.             SET @OrderExpression = 'ORDER BY ' + @OrderExpression  
  36.         END  
  37.     END  
  38.     PRINT @OrderExpression  
  39.  
  40.     IF @PageIndex IS NULL OR @PageIndex < 1 
  41.     BEGIN  
  42.         SET @PageIndex = 1 
  43.     END  
  44.     PRINT @PageIndex  
  45.     IF @PageSize IS NULL OR @PageSize < 1 
  46.     BEGIN  
  47.         SET @PageSize = 10 
  48.     END  
  49.     PRINT  @PageSize  
  50.  
  51.     DECLARE @SqlQuery VARCHAR(4000)  
  52.  
  53.     SET @SqlQuery='SELECT '+@SelectList+',RowNumber   
  54.     FROM   
  55.         (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber   
  56.           FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource   
  57.     WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)   
  58.     + ' AND ' +   
  59.     CAST((@PageIndex * @PageSize) AS VARCHAR)   
  60.     PRINT @SqlQuery  
  61.     SET NOCOUNT ON  
  62.     EXECUTE(@SqlQuery)  
  63.     SET NOCOUNT OFF  
  64.    
  65.     RETURN @@RowCount  
  66. END 

三、

从数据表中取出第n条到第m条的记录的方法

从publish 表中取出第 n 条到第 m 条的记录:

SELECT TOP m-n+1 *

FROM publish

WHERE (id NOT IN

    (SELECT TOP n-1 id

     FROM publish))

 

id 为publish 表的关键字

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

分页方案:

select top 页大小 *

from table1

where id>

(select max (id) from

(select top ((页码-1)*页大小) id from table1 order by id) as T

)

order by id

 

 

全部的sql语句:

 


  1. 代码  
  2.  
  3. --获取指定页的数据:  
  4. CREATE PROCEDURE pagination3  
  5. @tblName varchar(255), -- 表名  
  6. @strGetFields varchar(1000) = ''*'', -- 需要返回的列   
  7. @fldName varchar(255)='''', -- 排序的字段名  
  8. @PageSize int = 10, -- 页尺寸  
  9. @PageIndex int = 1, -- 页码  
  10. @doCount bit = 0, -- 返回记录总数, 非 0 值则返回  
  11. @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序  
  12. @strWhere varchar(1500) = '''' -- 查询条件 (注意: 不要加 where)  
  13. AS  
  14.  
  15. declare @strSQL varchar(5000) -- 主语句  
  16. declare @strTmp varchar(110) -- 临时变量  
  17. declare @strOrder varchar(400) -- 排序类型  
  18.  
  19. if @doCount != 0  
  20. begin  
  21. if @strWhere !=''''  
  22. set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere  
  23. else  
  24. set @strSQL = "select count(*) as Total from [" + @tblName + "]"  
  25. end  
  26. --以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况:  
  27. else  
  28. begin  
  29. if @OrderType != 0  
  30. begin  
  31. set @strTmp = "<(select min" 
  32. set @strOrder = " order by [" + @fldName +"] desc"  
  33. --如果@OrderType不是0,就执行降序,这句很重要!  
  34. end  
  35. else  
  36. begin  
  37. set @strTmp = ">(select max" 
  38. set @strOrder = " order by [" + @fldName +"] asc"  
  39. end  
  40.  
  41. if @PageIndex = 1 
  42. begin  
  43. if @strWhere != ''''   
  44.  
  45. set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  
  46.         from [" + @tblName + "] where " + @strWhere + " " + @strOrder  
  47. else  
  48.  
  49. set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "   
  50.         from ["+ @tblName + "] "+ @strOrder  
  51. --如果是第一页就执行以上代码,这样会加快执行速度  
  52. end  
  53. else  
  54. begin  
  55. --以下代码赋予了@strSQL以真正执行的SQL代码   
  56.    
  57. set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["  
  58. + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "])   
  59.       from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "]   
  60.       from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder  
  61.  
  62. if @strWhere != ''''  
  63. set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["  
  64. + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["  
  65. + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["  
  66. + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "  
  67. + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder  
  68. end   
  69.  
  70. end   
  71.  
  72. exec (@strSQL)  
  73.  
  74. GO