说明: 该存储过程支持单个表或者视图; 如果是多表关联的情况,需要将其先JOIN成一个视图.
Code
1 -- =============================================
2 -- 作者: Rickey Hu
3 -- 创建日期: 2009/11/05
4 -- 功能: 从一个表或视图中返回指定页大小的记录,同时返回满足要求的所以记录的行数.
5 -- 参数: TableName: 表名或者视图名称
6 -- PrimaryKey: 主键列名
7 -- PageIndex: 页号 (从1开始)
8 -- PageSize: 一页中的数据行数
9 -- WhereExpression: 过滤条件
10 -- SortExpression: 排序
11 -- 返回: 第一个结果集: 指定页的数据 (RowNum列为行号)
12 -- 第二个结果集/存储过程返回值: 记录集总行数
13 -- 注意: 1) 当PageIndex或者PageSize为非正数时,返回所有记录;
14 -- 2) 当WhereExpression为NULL或空时,不过滤数据;
15 -- 3) 当SortExpression为NULL或空时,默认为PrimaryKey的升序;
16 -- =============================================
17 CREATE PROCEDURE [dbo].[appsp_ListRetrieve]
18 @TableName varchar(50),
19 @PrimaryKey varchar(50),
20 @PageIndex int,
21 @PageSize int,
22 @WhereExpression varchar(max),
23 @SortExpression varchar(max)
24 AS
25 BEGIN
26 DECLARE @SQLCommand nvarchar(max)
27 DECLARE @tblTableInfo TABLE (
28 tblID int IDENTITY,
29 ColumnName varchar(500))
30 DECLARE @row int
31 DECLARE @rowMax int
32 DECLARE @ColumnSelect varchar(max)
33 DECLARE @Return int
34 DECLARE @ParmDefinition nvarchar(500)
35
36 -- Build the column names
37 INSERT INTO @tblTableInfo (ColumnName) SELECT '[' + [name] + ']' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE ([type] = 'U' or [type] = 'V') and [Name] = @TableName)
38 SET @row = 1
39 SET @rowMax = (SELECT max(tblId) FROM @tblTableInfo)
40 SET @ColumnSelect = ''
41 WHILE @row <= @rowMax
42 BEGIN
43 IF @ColumnSelect > '' SET @ColumnSelect = @ColumnSelect + ','
44 SET @ColumnSelect = @ColumnSelect + isNULL((SELECT ColumnName FROM @tblTableInfo WHERE tblID = @row),'')
45 SET @row = @row + 1
46 END
47
48 SET @ColumnSelect = 'SELECT ' + REPLACE(@ColumnSelect,'[timestamp]','cast([Timestamp] as bigint) as [Timestamp]')
49
50 -- Do the where clause
51 IF @WhereExpression > ''
52 BEGIN
53 -- Add in Enabled flag
54 IF left(@WhereExpression,6) <> 'WHERE ' SET @WhereExpression = 'WHERE ' + @WhereExpression
55 END
56
57 -- Do the sort expression
58 IF isNULL(@SortExpression,'') = '' SET @SortExpression = @PrimaryKey
59 IF left(@SortExpression,9) <> 'ORDER BY ' SET @SortExpression = 'ORDER BY ' + @SortExpression + ',' + @PrimaryKey
60
61 -- get paginated results
62 SET @SQLCommand = @ColumnSelect + ' FROM (SELECT TOP 10000000 *,
63 ROW_NUMBER() OVER(' + @SortExpression + ') as RowNum
64 FROM [' + @TableName + '] ' + isNULL(@WhereExpression,'') + ' ORDER BY RowNum ) As SelectList '
65 IF (@PageIndex>0 AND @PageSize>0)
66 BEGIN
67 SET @SQLCommand = @SQLCommand+ 'Where RowNum BETWEEN (('
68 + Convert(varchar, @PageIndex, 10) + ' - 1) * ' + Convert(varchar, @PageSize, 10) + ' + 1)
69 AND (' + Convert(varchar,@PageIndex, 1) + ' * ' + Convert(varchar, @PageSize, 10) + ')'
70 END
71
72 -- Execute the SQL query
73 EXEC sp_executesql @SQLCommand
74
75 -- Return the total number of rows
76 SET @SQLCommand = N'SELECT @returnOUT = Count(*) FROM [' + @TableName + '] ' + isNULL(@WhereExpression,'')
77 SET @ParmDefinition = N'@returnOUT varchar(30) OUTPUT';
78 EXECUTE sp_executesql @SQLCommand, @ParmDefinition, @returnOUT=@return OUTPUT;
79
80 SELECT @Return AS NumRecords
81
82 RETURN @Return
83 END
84
1 -- =============================================
2 -- 作者: Rickey Hu
3 -- 创建日期: 2009/11/05
4 -- 功能: 从一个表或视图中返回指定页大小的记录,同时返回满足要求的所以记录的行数.
5 -- 参数: TableName: 表名或者视图名称
6 -- PrimaryKey: 主键列名
7 -- PageIndex: 页号 (从1开始)
8 -- PageSize: 一页中的数据行数
9 -- WhereExpression: 过滤条件
10 -- SortExpression: 排序
11 -- 返回: 第一个结果集: 指定页的数据 (RowNum列为行号)
12 -- 第二个结果集/存储过程返回值: 记录集总行数
13 -- 注意: 1) 当PageIndex或者PageSize为非正数时,返回所有记录;
14 -- 2) 当WhereExpression为NULL或空时,不过滤数据;
15 -- 3) 当SortExpression为NULL或空时,默认为PrimaryKey的升序;
16 -- =============================================
17 CREATE PROCEDURE [dbo].[appsp_ListRetrieve]
18 @TableName varchar(50),
19 @PrimaryKey varchar(50),
20 @PageIndex int,
21 @PageSize int,
22 @WhereExpression varchar(max),
23 @SortExpression varchar(max)
24 AS
25 BEGIN
26 DECLARE @SQLCommand nvarchar(max)
27 DECLARE @tblTableInfo TABLE (
28 tblID int IDENTITY,
29 ColumnName varchar(500))
30 DECLARE @row int
31 DECLARE @rowMax int
32 DECLARE @ColumnSelect varchar(max)
33 DECLARE @Return int
34 DECLARE @ParmDefinition nvarchar(500)
35
36 -- Build the column names
37 INSERT INTO @tblTableInfo (ColumnName) SELECT '[' + [name] + ']' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE ([type] = 'U' or [type] = 'V') and [Name] = @TableName)
38 SET @row = 1
39 SET @rowMax = (SELECT max(tblId) FROM @tblTableInfo)
40 SET @ColumnSelect = ''
41 WHILE @row <= @rowMax
42 BEGIN
43 IF @ColumnSelect > '' SET @ColumnSelect = @ColumnSelect + ','
44 SET @ColumnSelect = @ColumnSelect + isNULL((SELECT ColumnName FROM @tblTableInfo WHERE tblID = @row),'')
45 SET @row = @row + 1
46 END
47
48 SET @ColumnSelect = 'SELECT ' + REPLACE(@ColumnSelect,'[timestamp]','cast([Timestamp] as bigint) as [Timestamp]')
49
50 -- Do the where clause
51 IF @WhereExpression > ''
52 BEGIN
53 -- Add in Enabled flag
54 IF left(@WhereExpression,6) <> 'WHERE ' SET @WhereExpression = 'WHERE ' + @WhereExpression
55 END
56
57 -- Do the sort expression
58 IF isNULL(@SortExpression,'') = '' SET @SortExpression = @PrimaryKey
59 IF left(@SortExpression,9) <> 'ORDER BY ' SET @SortExpression = 'ORDER BY ' + @SortExpression + ',' + @PrimaryKey
60
61 -- get paginated results
62 SET @SQLCommand = @ColumnSelect + ' FROM (SELECT TOP 10000000 *,
63 ROW_NUMBER() OVER(' + @SortExpression + ') as RowNum
64 FROM [' + @TableName + '] ' + isNULL(@WhereExpression,'') + ' ORDER BY RowNum ) As SelectList '
65 IF (@PageIndex>0 AND @PageSize>0)
66 BEGIN
67 SET @SQLCommand = @SQLCommand+ 'Where RowNum BETWEEN (('
68 + Convert(varchar, @PageIndex, 10) + ' - 1) * ' + Convert(varchar, @PageSize, 10) + ' + 1)
69 AND (' + Convert(varchar,@PageIndex, 1) + ' * ' + Convert(varchar, @PageSize, 10) + ')'
70 END
71
72 -- Execute the SQL query
73 EXEC sp_executesql @SQLCommand
74
75 -- Return the total number of rows
76 SET @SQLCommand = N'SELECT @returnOUT = Count(*) FROM [' + @TableName + '] ' + isNULL(@WhereExpression,'')
77 SET @ParmDefinition = N'@returnOUT varchar(30) OUTPUT';
78 EXECUTE sp_executesql @SQLCommand, @ParmDefinition, @returnOUT=@return OUTPUT;
79
80 SELECT @Return AS NumRecords
81
82 RETURN @Return
83 END
84