SQL Server 2005 建立 CLR 預存程序完整教學：從 VS 專案到部署

前置環境

• SQL Server 2005+，啟用 CLR：
  sp_configure 'clr enabled', 1; RECONFIGURE;
• Visual Studio 2005/2008/2010+ (SQL Server Database Project)。
• 測試資料庫 test，結構 user，資料表 test1，登入 test_user (sysadmin，供偵錯用)。

方法一：命令列編譯 + 手動部署 (推薦，彈性最高)

1. 建立 C# 類別庫專案，加入 StoredProcedures.cs：

using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void SelectAll()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM [user].test1", conn);
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
    }
}

2. 開啟 Visual Studio Developer Command Prompt 編譯：
   csc /t:library /out:testProcedure.dll testProcedure.cs
3. SSMS → 資料庫 test → 「組件」→ 右鍵「新增組件」→ 選取 testProcedure.dll → 確定。
4. 建立 T-SQL 包裝預存程序：

CREATE PROCEDURE [user].SelectAll
AS EXTERNAL NAME testProcedure.[StoredProcedures].SelectAll;
GO

5. 執行 EXEC [user].SelectAll; 即可看到結果。

方法二：Visual Studio 直接部署 (適合開發/偵錯)

1. 專案加入「偵錯指令碼」 (預存程序呼叫腳本)。
2. 設定專案屬性 → 「資料庫」→ 目標連線字串。
3. 「建置」→「部署」，VS 自動註冊組件、建立預存程序。
4. 可直接在 VS 中設定中斷點偵錯 CLR 程式碼。

常見偵錯錯誤與解決

錯誤：無法執行 sp_enable_sql_debug
原因：
1. 連線不穩定 / 防火牆阻擋。
2. 權限不足：VS 執行帳戶與 SQL 連線帳戶皆需為 sysadmin 角色。

CLR vs T-SQL 選擇指南

權限集合

• SAFE：預設，僅存取資料庫內部資源。
• EXTERNAL_ACCESS：存取檔案、網路、登錄等外部資源。
• UNSAFE：非受控程式碼，需 TRUSTWORTHY 或簽署非對稱金鑰。

CLR 預存程序擴展了資料庫程式設計能力，但慎用於核心業務邏輯，優先考慮 T-SQL 集合操作優勢。