根据表或者视图自动生成分页语句的存储过程，适用于Sql Server 2005及以上版本

时间：2011-02-18 来源：零度的火

对于一向尽力避免拼Sql语句执行的人，看到同事们在数据库中都利用拼Sql语句的形式进行分页操作。不仅写起来复杂，容易出错，而且各个表和视图都要重复的去写，于是萌生了写一个生成分页语句的存储过程的想法，而且分页不使用拼Sql语句的形式。用这个存储过程生成语句之后，再加上特定业务逻辑的Where条件之后，就可以用了。比较方便。

存储过程需要的参数有2个，即表或者视图的名称，以及需要显示和排序的列名组成的字符串，比如：

L_spCreatePagingScript 'vw_Person'
L_spCreatePagingScript 'vw_Person','FirstName,LastName,MiddleName'

生成的语句类似以下代码：

 1 /*
 2 DECLARE @OrderByColumnName VARCHAR(128)
 3 DECLARE @PageSize INT
 4 DECLARE @PageIndex INT -- started from 1
 5 DECLARE @IsDesc BIT    -- 0 or 1
 6 
 7 SET @OrderByColumnName = ''
 8 SET @PageSize = 10
 9 SET @PageIndex = 1
10 SET @IsDesc = 0
11  */
12  WITH DataTable AS
13 (
14     SELECT Row_Number() 
15         OVER (
16             ORDER BY
17                 (CASE @OrderByColumnName + Cast(@IsDesc AS VARCHAR(1))
18                     When 'FirstName0' Then FirstName
19                     When 'LastName0' Then LastName
20                     When 'MiddleName0' Then MiddleName End) ASC,
21                 (CASE @OrderByColumnName + Cast(@IsDesc AS VARCHAR(1))
22                     When 'FirstName1' Then FirstName
23                     When 'LastName1' Then LastName
24                     When 'MiddleName1' Then MiddleName End) DESC
25 ) AS RowNumber
26         , FirstName,LastName,MiddleName
27     FROM vw_Person
28     -- TODO: WHERE clauses here
29 )
30 SELECT *
31 FROM DataTable
32 WHERE RowNumber BETWEEN @PageSize * (@PageIndex - 1) + 1 AND @PageSize * @PageIndex

以下是源代码：

 1 CREATE PROCEDURE L_spCreatePagingScript
 2 (
 3     @TableName VARCHAR(128)
 4     , @ColumnNames VARCHAR(8000) = NULL    -- divided by ','
 5 )
 6 AS
 7 -- Create By Ricky Lin, 2/18/2011, ricky81317.cnblogs.com
 8 SET NOCOUNT ON
 9 CREATE TABLE #ColumnNames
10 (
11     ColumnName VARCHAR(128)
12 )
13 
14 IF @ColumnNames IS NULL OR Len(@ColumnNames) = 0
15 BEGIN
16     -- let's generate all column names
17     INSERT INTO #ColumnNames
18     SELECT [Name] FROM Sys.Columns WHERE [Object_ID] = Object_ID(@TableName)
19     
20     SET @ColumnNames = ''
21     SELECT @ColumnNames = @ColumnNames + ', ' + ColumnName FROM #ColumnNames
22     IF Len(@ColumnNames) > 0 
23         SET @ColumnNames = Right(@ColumnNames, Len(@ColumnNames) - 2)
24 END
25 ELSE
26 BEGIN
27     INSERT INTO #ColumnNames
28     SELECT Item FROM dbo.K_fnStringSplit(@ColumnNames, ',', 0)
29 END
30 
31 -- SELECT * FROM #ColumnNames
32 -- PRINT @ColumnNames
33 
34 DECLARE @CaseStr VARCHAR(8000)
35 DECLARE @AscStr VARCHAR(8000)
36 DECLARE @DescStr VARCHAR(8000)
37 SET @CaseStr = '
38                 (CASE @OrderByColumnName + Cast(@IsDesc AS VARCHAR(1))'
39 SET @AscStr = ''
40 SET @DescStr = ''
41 
42 DECLARE @ColName VARCHAR(128)
43 DECLARE colCur CURSOR FAST_FORWARD FOR SELECT ColumnName FROM #ColumnNames
44 OPEN colCur
45 FETCH NEXT FROM colCur INTO @ColName
46 WHILE @@Fetch_Status = 0
47 BEGIN
48     SET @AscStr = @AscStr + '
49                     When ''' + @ColName + '0'' Then ' + @ColName
50     SET @DescStr = @DescStr + '
51                     When ''' + @ColName + '1'' Then ' + @ColName
52     FETCH NEXT FROM colCur INTO @ColName
53 END
54 CLOSE colCur
55 DEALLOCATE colCur
56 
57 SET @AscStr = @CaseStr + @AscStr + ' End) ASC,'
58 SET @DescStr = @CaseStr + @DescStr + ' End) DESC
59 '
60 
61 DROP TABLE #ColumnNames
62 
63 -- PRINT @OrderByStr
64 
65 DECLARE @Sql VARCHAR(MAX)
66 SET @Sql = '
67 /*
68 DECLARE @OrderByColumnName VARCHAR(128)
69 DECLARE @PageSize INT
70 DECLARE @PageIndex INT -- started from 1
71 DECLARE @IsDesc BIT    -- 0 or 1
72 
73 SET @OrderByColumnName = ''''
74 SET @PageSize = 10
75 SET @PageIndex = 1
76 SET @IsDesc = 0
77 */
78 WITH DataTable AS
79 (
80     SELECT Row_Number() 
81         OVER (
82             ORDER BY' + @AscStr + @DescStr + ') AS RowNumber
83         , ' + @ColumnNames + '
84     FROM ' + @TableName + '
85     -- TODO: WHERE clauses here
86 )
87 SELECT *
88 FROM DataTable
89 WHERE RowNumber BETWEEN @PageSize * (@PageIndex - 1) + 1 AND @PageSize * @PageIndex'
90 
91 PRINT @Sql
92 
93 SET NOCOUNT OFF