1 alter PROCEDURE fTable_AllSns
2 (
3 @PageSize int, --页大小
4 @PageIndex int, --页码
5 @SnsName nvarchar(300),
6 @DoCount int, --是否求总数
7 @TotalRecord int output
8 )
9 AS
10 SET NOCOUNT ON
11
12 DECLARE @PageLowerBound int
13 DECLARE @PageUpperBound int
14 DECLARE @RowsToReturn int
15 DECLARE @MoreRecords int
16 DECLARE @GetTotalRecordSql nvarchar(2000)
17
18 SET @TotalRecord=0
19 SET @RowsToReturn = @PageSize * @PageIndex
20 SET @MoreRecords = @RowsToReturn + 1
21 SET ROWCOUNT @MoreRecords
22
23
24 SET @PageLowerBound = @PageSize * (@PageIndex-1)
25 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
26
27
28 DECLARE @IndexTable TABLE(
29 IndexID int identity(1,1), --排序序号
30 SnsID int default 0,
31 SnsTypeName nvarchar(50),
32 CreateTime datetime
33
34 ) --定义表变量
35 INSERT INTO
36 @IndexTable(SnsID,SnsTypeName,CreateTime)
37 SELECT
38 s.SnsID,sc.SnsTypeName,sc.CreateTime
39 FROM
40 fTable_Sns s inner join fTable_SnsCategory sc
41 ON s.SnsTypeID=sc.SnsTypeID
42
43 ORDER BY
44 sc.CreateTime desc
45 IF(@DoCount=1)
46 select @TotalRecord=count(*) from @IndexTable
47
48
49
50
51 IF @SnsName IS NOT NULL ---不为空
52 begin
53 SELECT
54 s.SnsID,s.SnsName,s.SnsEnName,s.MemberCount,s.ViewCount,SnsDescription,IndexTable.SnsTypeName
55
56
57 FROM
58 @IndexTable IndexTable,
59 fTable_Sns s
60 WHERE
61 s.SnsID = IndexTable.SnsID AND s.SnsName like '%'+@SnsName+'%' AND
62 IndexTable.IndexID > @PageLowerBound AND
63 IndexTable.IndexID < @PageUpperBound
64 ORDER BY
65 IndexTable.IndexID
66 end
67
68 IF @SnsName IS NULL ---为空
69 begin
70 SELECT
71 s.SnsID,s.SnsName,s.SnsEnName,s.MemberCount,s.ViewCount,SnsDescription,IndexTable.SnsTypeName
72
73
74 FROM
75 @IndexTable IndexTable,
76 fTable_Sns s
77 WHERE
78 s.SnsID = IndexTable.SnsID AND
79 IndexTable.IndexID > @PageLowerBound AND
80 IndexTable.IndexID < @PageUpperBound
81 ORDER BY
82 IndexTable.IndexID
83 end
84
85
86
87
88 SET NOCOUNT OFF
89
90
91
92 GO
93
2 (
3 @PageSize int, --页大小
4 @PageIndex int, --页码
5 @SnsName nvarchar(300),
6 @DoCount int, --是否求总数
7 @TotalRecord int output
8 )
9 AS
10 SET NOCOUNT ON
11
12 DECLARE @PageLowerBound int
13 DECLARE @PageUpperBound int
14 DECLARE @RowsToReturn int
15 DECLARE @MoreRecords int
16 DECLARE @GetTotalRecordSql nvarchar(2000)
17
18 SET @TotalRecord=0
19 SET @RowsToReturn = @PageSize * @PageIndex
20 SET @MoreRecords = @RowsToReturn + 1
21 SET ROWCOUNT @MoreRecords
22
23
24 SET @PageLowerBound = @PageSize * (@PageIndex-1)
25 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
26
27
28 DECLARE @IndexTable TABLE(
29 IndexID int identity(1,1), --排序序号
30 SnsID int default 0,
31 SnsTypeName nvarchar(50),
32 CreateTime datetime
33
34 ) --定义表变量
35 INSERT INTO
36 @IndexTable(SnsID,SnsTypeName,CreateTime)
37 SELECT
38 s.SnsID,sc.SnsTypeName,sc.CreateTime
39 FROM
40 fTable_Sns s inner join fTable_SnsCategory sc
41 ON s.SnsTypeID=sc.SnsTypeID
42
43 ORDER BY
44 sc.CreateTime desc
45 IF(@DoCount=1)
46 select @TotalRecord=count(*) from @IndexTable
47
48
49
50
51 IF @SnsName IS NOT NULL ---不为空
52 begin
53 SELECT
54 s.SnsID,s.SnsName,s.SnsEnName,s.MemberCount,s.ViewCount,SnsDescription,IndexTable.SnsTypeName
55
56
57 FROM
58 @IndexTable IndexTable,
59 fTable_Sns s
60 WHERE
61 s.SnsID = IndexTable.SnsID AND s.SnsName like '%'+@SnsName+'%' AND
62 IndexTable.IndexID > @PageLowerBound AND
63 IndexTable.IndexID < @PageUpperBound
64 ORDER BY
65 IndexTable.IndexID
66 end
67
68 IF @SnsName IS NULL ---为空
69 begin
70 SELECT
71 s.SnsID,s.SnsName,s.SnsEnName,s.MemberCount,s.ViewCount,SnsDescription,IndexTable.SnsTypeName
72
73
74 FROM
75 @IndexTable IndexTable,
76 fTable_Sns s
77 WHERE
78 s.SnsID = IndexTable.SnsID AND
79 IndexTable.IndexID > @PageLowerBound AND
80 IndexTable.IndexID < @PageUpperBound
81 ORDER BY
82 IndexTable.IndexID
83 end
84
85
86
87
88 SET NOCOUNT OFF
89
90
91
92 GO
93