原创。如果使用本文源代码，请保留作者信息。谢谢
对于一向尽力避免拼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