SQL SERVER2005存储过程分页

因业务需要写了一个通用的分页存储过程，实现了动态传入检索列及表名等，能返回总条数、分页数及某页的数据。

 1 CREATE PROCEDURE [dbo].[GetPageData]
 2 ( 
 3     --表名(必填)
 4     @tblName varchar(255),
 5 
 6     --检索列 (必填)
 7     @strGetFields varchar(500) = '*',
 8 
 9     --排序字段列(分页用)(必填)
10     @pageSort varchar(100)=null,
11 
12     --排序字段列(页内用)
13     @Sort varchar(150)=null,
14 
15     --每页显示的记录个数 (必填)
16     @pageSize int = 10,
17 
18     --要显示哪一页的记录 (必填)
19     @pageIndex int = 1,
20 
21     --查询条件 (注意: 不要加 where)
22     @strWhere varchar(1500) = null,
23 
24     --查询结果总记录数
25     @totalCnt int = 0 output, 
26 
27     --查询结果分页后的总页数 
28     @pageCnt int = 1 output
29 )
30 
31 AS
32 BEGIN 
33     SET NOCOUNT ON 
34     --主语句
35     declare @strSQL nvarchar(4000)
36     --存放取得查询结果总数的查询语句
37     declare @strSQLCnt nvarchar(2000)
38     --查询结果总记录数(临时变量)
39     declare @tmpCounts int 
40     --所取出记录的下限
41     declare @PageLowerBound int
42     --所取出记录的上限
43     declare @PageUpperBound int
44 
45     --------生成总数查询语句--------
46     if @strWhere is null or @strWhere=''
47         begin 
48             set @strSQLCnt=N'SELECT @totalCnt=count(*) FROM '+@tblName 
49         end 
50     else 
51         begin 
52             set @strSQLCnt=N'SELECT @totalCnt=count(*) FROM '+@tblName + ' WHERE ' +@strWhere
53         end 
54 
55     --------取得结果集总数量--------
56     exec sp_executesql @strSQLCnt,N'@totalCnt int out ',@totalCnt out 
57 
58     --------取得分页总数--------
59     if @totalCnt = 0 
60         set @tmpCounts = 1 
61     else 
62         set @tmpCounts = @totalCnt 
63     if @pageSize < 1
64         set @pageSize = 1
65     set @pageCnt=(@tmpCounts+@pageSize-1)/@pageSize 
66 
67     --------当前页校验--------
68     --当前页小于第一页，取第一页
69     if @pageIndex < 1
70         set @pageIndex=1 
71     --当前页大于总页数，取最后一页
72     if @pageIndex>@pageCnt 
73         set @pageIndex=@pageCnt 
74 
75     --计算上下限的值
76     SET @PageUpperBound=@pageIndex * @pageSize
77     SET @PageLowerBound=@PageUpperBound - @PageSize + 1
78 
79     --------生成分页数据查询语句--------
80     if @pageSort is not null and @pageSort!=''
81         begin
82             set @strSQL=N'SELECT '+ @strGetFields + ' FROM (SELECT '+ @strGetFields + ',row_number() over(order by '+ @pageSort +') as rownum FROM '+ @tblName 
83             if @strWhere is null or @strWhere=''
84                 set @strSQL=@strSQL + N') as t'
85             else
86                 set @strSQL=@strSQL + N' WHERE ' + @strWhere + N') as t'
87             set @strSQL=@strSQL + N' WHERE rownum between '+convert(varchar,@PageLowerBound)+ ' and '+convert(varchar,@PageUpperBound)
88             if @Sort is not null or @Sort!=''
89                 set @strSQL=@strSQL+N' ORDER BY '+@Sort
90         end
91 
92     --------返回查询结果--------
93     exec sp_executesql @strSQL
94     SET NOCOUNT OFF 
95 END
posted @ 2013-07-12 13:57  堕落魂伤  阅读(376)  评论(0)    收藏  举报