1 CREATE PROCEDURE [dbo].[Pro_GetPageData]
2
3 (
4
5 @TableName nvarchar(3000), -- 表名
6
7 @ReturnFields nvarchar(3000) = '*', -- 需要返回的列
8
9 @PageSize int = 10, -- 每页记录数
10
11 @PageIndex int = 0, -- 当前页码
12
13 @Where nvarchar(3000) = '', -- 查询条件
14
15 @OrderBy nvarchar(200), -- 排序字段名 最好为唯一主键
16
17 @OrderType int = 1 -- 排序类型 1:降序 其它为升序
18
19 )
20
21 AS
22
23 DECLARE @TotalRecord int
24
25 DECLARE @TotalPage int
26
27 DECLARE @CurrentPageSize int
28
29 DECLARE @TotalRecordForPageIndex int
30
31 declare @CountSql nvarchar(4000)
32
33
34
35 if @OrderType = 1
36
37 BEGIN
38
39 set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' desc,') + ' desc '
40
41 END
42
43 else
44
45 BEGIN
46
47 set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' asc,') + ' asc '
48
49 END
50
51
52
53 -- 总记录
54
55 set @CountSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
56
57 execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out
58
59
60
61 SET @TotalPage=(@TotalRecord-1)/@PageSize+1
62
63
64
65 -- 查询页数不得大于总页数
66
67 if(@PageIndex > @TotalPage)
68
69 set @PageIndex = @TotalPage
70
71
72
73 SET @CurrentPageSize=(@PageIndex-1)*@PageSize
74
75
76
77
78
79 -- 返回记录
80
81 set @TotalRecordForPageIndex=@PageIndex*@PageSize
82
83
84
85 exec ('SELECT *
86
87 FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS ROWNUM
88
89 FROM '+@TableName+ ' ' + @Where +' ) AS TempTable
90
91 WHERE TempTable.ROWNUM >
92
93 '+@CurrentPageSize)
94
95
96
97
98
99 -- 返回总页数和总记录
100
101 SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
