【SQL教程】sql Sever 创建分页储存过程及直接查询

wangy

1. 
   
2. --创建存储过程
     [3 ^" g1 ]& y( O. W/ f
3. if exists(select * from sysobjects where name='P_GetStudentInfoPage')
   9 O7 [% n1 G; a3 }- A( J8 c* L% ]
4. drop proc P_GetStudentInfoPage
   
5. go
   
6. create proc [dbo].P_GetStudentInfoPage
   
7. (
   # L  f+ `$ t) ?
8.         @pageIndex int=1,--页号
   ; a: N& F2 R( s  ?6 M
9.         @pageSize int=10--每页数据条数
   $ x) @/ ]( }9 J) j
10. )
    4 O* m% I2 Z8 Q5 [! I/ K
11. as
    
12.         --select count(*) from StudentInfo  --查询数据总数
    
13. 
    
14.         select * from
    & n  O  x% p9 ^# w; ~& p
15.                 (select ROW_NUMBER() over(order by StudentNo) as rowNum,* from StudentInfo) as tbl
    1 w' _8 N8 _2 g5 O
16.                 where tbl.rowNum between ((@pageIndex - 1)* @pageSize + 1) and (@pageIndex*@pageSize)
    & i) R- ]& \# N4 M( p; Z) i
17. 
    ( t; w6 G6 r: V* w5 `7 H
18. GO
    : f, _  i+ i$ H! W! M  K! m
19. 
    
20. --调用
    7 F& Y4 y6 |' j# M. X" w
21. 
    
22. EXEC P_GetStudentInfoPage 1,10
    
23. 
    2 f2 Y* q! l9 m4 s
24. EXEC P_GetStudentInfoPage 2,10
    
25. 
    
26. --------------------------------------直接查询代码
      Y2 E6 s5 y' u* T  l; t/ x
27. select * from StudentInfo where id>=1 and id<=10
    ! g5 ?% h9 j* c5 d- W! f% W: b
28. select * from StudentInfo where id>=10 and id<=20
    
29. 
    ! Z1 B; k# R" S% f1 L" d
30. select * from StudentInfo where id between (1-1)*10+1 and 10 *1
    
31. select * from StudentInfo where id between (2-1)*10+1 and 10 *2
      r! ]* ~' B9 s7 P
32. 
    " t8 N; e* e0 K2 w$ e1 [+ o" ~
33. select (1-1)*10+1 --1
    
34. select (2-1)*10+1 --11
    # c" P/ ]! f* I6 V
35. select (3-1)*10+1 --21
    
36. 
    + P8 X: s2 p/ A
37. select * from StudentInfo where id between 11 and 20
    0 y9 e, {$ J6 I, z+ R! U
38. 
    7 O7 f5 |5 _& I: S
39. select * from
    + }6 s. D5 D7 c4 ?9 y' N
40. (select ROW_NUMBER() over(order by StudentNo desc) newRows,* from StudentInfo) t
    
41. where t.newRows between (1-1)*10+1 and 10 *1

复制代码