如何從 SQL Server 預存程序呼叫 API (Calling an API from SQL Server stored procedure)

在 SQL Server 預存程序中呼叫 API，通常需要使用 SQL Server 的內建功能，例如 sp_OA* 系列存儲過程 或基於 CLR 的自定義功能。以下是幾種實現方式及其步驟。

---

方法 1：使用 sp_OA* 系列存儲過程

SQL Server 內建了 sp_OACreate 和 sp_OAMethod 等存儲過程，可以用來呼叫外部 COM 物件（例如用於發送 HTTP 請求）。

範例：透過 sp_OA* 呼叫 REST API

sql

CREATE PROCEDURE CallExternalAPI
AS
BEGIN
    DECLARE @Object INT
    DECLARE @ResponseText NVARCHAR(MAX)
    DECLARE @URL NVARCHAR(200) = 'https://api.example.com/data'

    -- 創建對象
    EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT

    -- 發送 HTTP GET 請求
    EXEC sp_OAMethod @Object, 'Open', NULL, 'GET', @URL, FALSE
    EXEC sp_OAMethod @Object, 'Send'

    -- 獲取響應
    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    -- 打印響應內容
    PRINT @ResponseText

    -- 清理對象
    EXEC sp_OADestroy @Object
END
GO

注意

• 安全性：sp_OA* 功能默認是禁用的，因為它存在安全風險。您需要啟用它才能使用：

  sql
  
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'Ole Automation Procedures', 1;
  RECONFIGURE;
  

• 限制：sp_OA* 的性能和功能有限，不建議用於頻繁或複雜的API調用。

---

方法 2：使用 CLR (Common Language Runtime) 自定義函數

CLR允許在SQL Server中使用C#或其他.NET語言編寫自定義功能，並執行外部操作，例如呼叫API。

步驟

1. 啟用CLR集成

   sql
   
   EXEC sp_configure 'clr enabled', 1;
   RECONFIGURE;
   

2. 建立CLR專案

   • 使用Visual Studio建立一個C#類庫專案。
   • 新增System.Net.Http命名空間來執行HTTP請求。

   範例C#程式碼：

   csharp
   
   using System;
   using System.Net.Http;
   using System.Threading.Tasks;
   using Microsoft.SqlServer.Server;
   
   public class ApiCaller
   {
       [SqlFunction]
       public static string CallAPI(string url)
       {
           using (HttpClient client = new HttpClient())
           {
               var response = client.GetAsync(url).Result;
               return response.Content.ReadAsStringAsync().Result;
           }
       }
   }
   

3. 部署CLR程序集

   • 編譯專案，生成DLL。
   • 將DLL部署到SQL Server：

     sql
     
     CREATE ASSEMBLY ApiCaller
     FROM 'C:\Path\To\Your\ApiCaller.dll'
     WITH PERMISSION_SET = UNSAFE;
     

4. 創建SQL Server函數

   sql
   
   CREATE FUNCTION CallAPI(@Url NVARCHAR(MAX))
   RETURNS NVARCHAR(MAX)
   AS EXTERNAL NAME ApiCaller.[Namespace.ClassName].CallAPI;
   

5. 呼叫API

   sql
   
   SELECT dbo.CallAPI('https://api.example.com/data');
   

---

方法 3：使用外部工具 (例如 PowerShell) 配合 SQL Agent Job

如果API調用較複雜，可以考慮將邏輯交由外部工具處理，然後通過SQL Server觸發。

範例：使用PowerShell呼叫API

1. 建立PowerShell腳本

   powershell
   
   $url = "https://api.example.com/data"
   $response = Invoke-RestMethod -Uri $url -Method Get
   Write-Output $response
   

2. 通過SQL Server Agent執行腳本

   • 新建一個SQL Agent Job。
   • 在「步驟」中，選擇「PowerShell」作為執行類型，並填入上述腳本。

3. 將結果插入資料表 在PowerShell腳本中，將API回應結果寫入資料庫：

   powershell
   
   $url = "https://api.example.com/data"
   $response = Invoke-RestMethod -Uri $url -Method Get
   
   # 插入到SQL Server
   $connectionString = "Server=YourServer;Database=YourDatabase;Integrated Security=True;"
   $query = "INSERT INTO ApiResponseTable (ResponseData) VALUES ('$response')"
   Invoke-Sqlcmd -Query $query -ConnectionString $connectionString
   

---

最佳實踐

• 使用CLR或外部工具：sp_OA* 方法僅適用於簡單場景，CLR 或外部工具更適合頻繁或複雜的需求。
• 錯誤處理：無論使用哪種方法，都需要加入錯誤處理機制，避免API失敗影響SQL Server的穩定性。
• 安全性考量：避免將敏感資訊（例如API密鑰）直接硬編碼在程式中。

選擇適合您的業務需求和基礎設施的方式來實現API調用。