本存儲過程代碼生成器能處理如下幾種形式的存儲過程：
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=)