本存储过程代码生成器能处理如下几种形式的存储过程：
1、形如InsertTableName的存储过程，用于插入某条记录。
2、形如UpdateTableNameByFieldName的存储过程，用于根据某个字段更新某条记录。
3、形如DeleteTableNameByFieldName的存储过程，用于根据某个字段删除某条记录。
4、形如GetTableNames的存储过程，用于查询所有记录。
5、形如GetTableNamesByFieldName的存储过程，用于根据某个字段查询记录。
6、形如GetTableNamesByPage的存储过程，用于根据页码查询记录。
7、形如GetTableNamesByFilter的存储过程，用于根据过滤条件、页码查询记录。
在存储过程没有过多业务逻辑的情况下，一般上述集中形式的存储过程即能满足一般系统的需要。如果您需要支持更多的形式，请自行修改代码。
各种形式的存储过程举例如下：
1、形如InsertTableName的存储过程。对该存储过程，我们主要需要获取该表的字段名、类型和长度等。在以前，我们需要通过读取数据的Schema的方式来获得字段的这些属性，现在有了LINQ，我就可以直接从DBML文件中获取了。
1 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsertFunction')
2 BEGIN
3 DROP Procedure [InsertFunction]
4 END
5
6 GO
7
8 CREATE Procedure [InsertFunction]
9 (
10 @FunctionId Int = NULL,
11 @FunctionName NVarChar(50) = NULL,
12 @FunctionDescription NVarChar(255) = NULL
13 )
14
15 AS
16
17 BEGIN
18 INSERT INTO [Function] ([FunctionId],
19 [FunctionName],
20 [FunctionDescription])
21 VALUES (@FunctionId,
22 @FunctionName,
23 @FunctionDescription)
24 END
25
26 GO
获取了足够的信息后，这个存储过程的拼凑就可以完成了。主体拼凑代码如下，其中24和16用于生成空格，以便看到上面每个字段分一行的整齐代码。
1 strContentes += string.Format(" INSERT INTO [{0}] ({1})" + "\n", table, strColumns.GetColumns(24 + table.Length));
2 strContentes += string.Format(" VALUES ({0})" + "\n", strColumns.GetVariables(16));
2、形如GetTableNamesByFilter的存储过程。在有很多可选过滤条件的情况下，我们不可能为每一种组合创建一个存储过程，这时，我们可以采用拼凑Where后面的SQL语句的方式实现查询。这时，我们除了跟上一个存储过程一样获取字段信息外，还需要处理翻页、拼凑SQL语句等。我们可以采用嵌套Select语句的方式实现拼凑和翻页。
1 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetFunctionsByFilter')
3 DROP Procedure [GetFunctionsByFilter]
8 CREATE Procedure [GetFunctionsByFilter]
10 @FunctionName NVarChar(50) = NULL,
11 @PageSize Int = NULL,
12 @PageIndex Int = NULL,
13 @RecordCount Int = NULL OUTPUT
14 )
15
16 AS
17
18 BEGIN
19 DECLARE @MinIndex Int
20 DECLARE @MaxIndex Int
21 SET @MinIndex = (@PageIndex - 1) * @PageSize + 1
22 SET @MaxIndex = @MinIndex + @PageSize - 1
23
24 DECLARE @Where NVarChar(MAX)
25 SET @Where = '0 = 0'
26 IF @FunctionName IS NOT NULL
27 SET @Where = @Where + ' AND [Function].[FunctionName] LIKE ''%' + @FunctionName + '%'''
28
29 DECLARE @Record NVarChar(MAX)
30 SET @Record = 'SELECT ROW_NUMBER() OVER(ORDER BY [Function].[FunctionId]) AS [Index],
31 [Function].[FunctionId],
32 [Function].[FunctionName],
33 [Function].[FunctionDescription]
34 FROM [Function]
35 WHERE' + ' ' + @Where
36
37 DECLARE @Sql NVarChar(MAX)
38 SET @Sql = 'SELECT @RecordCount = COUNT(*)
39 FROM (' + @Record + ') DERIVEDTBL
40
41 SELECT [FunctionId],
42 [FunctionName],
43 [FunctionDescription]
44 FROM (' + @Record + ') DERIVEDTBL
45 WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'
46
47 DECLARE @Parameter NVarChar(MAX)
48 SET @Parameter = '@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'
49
50 EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount OUTPUT
51 END
52
53 GO
获取了足够的信息后，这个存储过程的拼凑就可以完成了。主体拼凑代码如下。值得注意的是，当可选参数为String类型时，您需要处理脚本注入的问题（笔者这里并未处理）。
1 strContentes = strContentes + " DECLARE @MinIndex Int" + "\n";
2 strContentes = strContentes + " DECLARE @MaxIndex Int" + "\n";
3 strContentes = strContentes + " SET @MinIndex = (@PageIndex - 1) * @PageSize + 1" + "\n";
4 strContentes = strContentes + " SET @MaxIndex = @MinIndex + @PageSize - 1" + "\n";
5 strContentes = strContentes + " " + "\n";
6 strContentes = strContentes + " DECLARE @Where NVarChar(MAX)" + "\n";
7 strContentes = strContentes + " SET @Where = '0 = 0'" + "\n";
8
9 for (int i = 0; i < method.GetParameters().Length - 3; i++)
10 {
11 strContentes += string.Format(" IF @{0} IS NOT NULL" + "\n", method.GetParameters()[i].GetName());
12
13 if (method.GetParameters()[i].ParameterType.ToSqlDbType().ToString() == "NVarChar")
14 {
15 strContentes += string.Format(" SET @Where = @Where + ' AND [{0}].[{1}] LIKE ''%' + @{2} + '%'''" + "\n", table, method.GetParameters()[i].GetName(), method.GetParameters()[i].GetName());
16 }
17 else if (method.GetParameters()[i].ParameterType.ToSqlDbType().ToString() == "DateTime" && method.GetParameters()[i].GetName().StartsWith("Begin") == true)
18 {
19 strContentes += string.Format(" SET @Where = @Where + ' AND [{0}].[{1}] >= ''' + CONVERT(NVarChar, @{2}, 101) + ' ' + '00:00:00' + ''''" + "\n", table, method.GetParameters()[i].GetName().Substring(5), method.GetParameters()[i].GetName());
20 }
21 else if (method.GetParameters()[i].ParameterType.ToSqlDbType().ToString() == "DateTime" && method.GetParameters()[i].GetName().StartsWith("End") == true)
22 {
23 strContentes += string.Format(" SET @Where = @Where + ' AND [{0}].[{1}] <= ''' + CONVERT(NVarChar, @{2}, 101) + ' ' + '23:59:59' + ''''" + "\n", table, method.GetParameters()[i].GetName().Substring(3), method.GetParameters()[i].GetName());
24 }
25 else
26 {
27 strContentes += string.Format(" SET @Where = @Where + ' AND [{0}].[{1}] = ''' + CONVERT(NVarChar, @{2}) + ''''" + "\n", table, method.GetParameters()[i].GetName(), method.GetParameters()[i].GetName());
28 }
29 }
30
31 strContentes = strContentes + " " + "\n";
32 strContentes = strContentes + " DECLARE @Record NVarChar(MAX)" + "\n";
33 strContentes += string.Format(" SET @Record = 'SELECT ROW_NUMBER() OVER(ORDER BY [{0}].[{1}]) AS [Index]," + "\n", table, strKey);
34 strContentes += string.Format(" {0}" + "\n", strColumns.GetColumns(table, infos, 30, true));
35 strContentes += string.Format(" FROM {0}" + "\n", infos.GetFroms(table, 23));
36 strContentes = strContentes + " WHERE' + ' ' + @Where" + "\n";
37 strContentes = strContentes + " " + "\n";
38 strContentes = strContentes + " DECLARE @Sql NVarChar(MAX)" + "\n";
39 strContentes = strContentes + " SET @Sql = 'SELECT @RecordCount = COUNT(*)" + "\n";
40 strContentes = strContentes + " FROM (' + @Record + ') DERIVEDTBL" + "\n";
41 strContentes = strContentes + " " + "\n";
42 strContentes += string.Format(" SELECT {0}" + "\n", strColumns.GetColumns(table, infos, 27, false));
43 strContentes = strContentes + " FROM (' + @Record + ') DERIVEDTBL" + "\n";
44 strContentes = strContentes + " WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'" + "\n";
45 strContentes = strContentes + " " + "\n";
46 strContentes = strContentes + " DECLARE @Parameter NVarChar(MAX)" + "\n";
47 strContentes = strContentes + " SET @Parameter = '@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'" + "\n";
48 strContentes = strContentes + " " + "\n";
49 strContentes = strContentes + " EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount OUTPUT" + "\n";
其它类型的存储过程我就不一一列举。
在开始使用这个代码生成器之前，请先手工完成Eallies.OA.DAL.Interface这个项目，这个项目包含所有的DAL层需要实现的接口。这个代码生成将根据这个接口生成相应名称、参数等的存储过程。
本存储过程代码生成器的调用方法请参考Eallies.OA.Generator目录下的GenerateProcedure.bat文件。
本文转自 Eallies 51CTO博客，原文链接：http://blog.51cto.com/eallies/79013，如需转载请自行联系原作者
![SQL优化SQL语句优化的目的[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)