sql server分页存储过程

利用表变量实现分页

一、

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