今天把项目中的存储过程抠出来学习了下,发现确实是一个可以通吃全部的分页存储过程。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66 |
Create PROCEDURE [dbo].[SqlDataPaging] @tbName varchar(255), --视图名 此处为自己定义的视图 所以该存储过程才能通吃全部 就是不晓得性能如何 @tbFields varchar(1000) = ‘*‘, --字段名(全部字段为*) @orderFiled varchar(5000), --排序字段(必须!支持多字段) @orderType int, --排序类型,1是升序,0是降序 @strWhere varchar(5000) = Null, --条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int
= 1, --指定当前为第几页 @pageRecord int
output
--输出记录总数 ASBEGIN Begin
Tran --开始事务 Declare
@sql nvarchar(4000) Declare
@TotalPage int
--返回总页数 --计算总记录数 if (@strWhere = ‘‘
or @strWhere = NULL) set
@sql = ‘select @pageRecord = count(*) from ‘
+ @tbName else set
@sql = ‘select @pageRecord = count(*) from ‘
+ @tbName + ‘ where ‘
+ @strWhere EXEC
sp_executesql @sql,N‘@pageRecord int OUTPUT‘,@pageRecord OUTPUT --计算总页数 select
@TotalPage=CEILING((@pageRecord+0.0)/@PageSize) if (@strWhere = ‘‘
or @strWhere = NULL) if (@orderType = 1) set
@sql = ‘Select * FROM (select ROW_NUMBER() Over(order by ‘
+ @orderFiled + ‘) as rowId,‘
+ @tbFields + ‘ from ‘
+ @tbName else set
@sql = ‘Select * FROM (select ROW_NUMBER() Over(order by ‘
+ @orderFiled + ‘ desc) as rowId,‘
+ @tbFields + ‘ from ‘
+ @tbName else if (@orderType = 1) set
@sql = ‘Select * FROM (select ROW_NUMBER() Over(order by ‘
+ @orderFiled + ‘) as rowId,‘
+ @tbFields + ‘ from ‘
+ @tbName + ‘ where ‘
+ @strWhere else set
@sql = ‘Select * FROM (select ROW_NUMBER() Over(order by ‘
+ @orderFiled + ‘ desc) as rowId,‘
+ @tbFields + ‘ from ‘
+ @tbName + ‘ where ‘
+ @strWhere --处理页数超出范围情况 if @PageIndex<=0 Set
@pageIndex = 1 if @pageIndex>@TotalPage Set
@pageIndex = @TotalPage --处理开始点和结束点 Declare
@StartRecord int Declare
@EndRecord int set
@StartRecord = (@pageIndex-1)*@PageSize + 1 set
@EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 set
@Sql = @Sql + ‘) as t where rowId between ‘
+ Convert(varchar(50),@StartRecord) + ‘ and ‘
+ Convert(varchar(50),@EndRecord) print @sql exec(@Sql) --------------------------------------------------- If @@Error <> 0 Begin RollBack
Tran Return
-1 End Else Begin Commit
Tran EndEND |
原文：http://www.cnblogs.com/Rock-Lee/p/3617517.html