SQLCLR 開發系列（二）：預存程序與使用者定義函數完整比較

前言

在 SQLCLR 中，預存程序與使用者定義函數 (UDF) 均以靜態方法實作，差別僅在於標註屬性：[SqlProcedure] 與 [SqlFunction]。UDF 又分為表值函數 (TVF) 與純量函數。本文將透過完整程式碼範例，詳細比較三者的開發模式、部署設定與呼叫方式。

一、CLR 預存程序

程式碼實作

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TestStoredProcedure(string name, ref string outstr)
    {
        outstr = "hello," + name;

        using (SqlConnection cn = new SqlConnection())
        {
            cn.ConnectionString = "context connection=true";
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "Select * from userinfo";
                cn.Open();
                SqlContext.Pipe.Send(cmd.ExecuteReader());
            }
        }
    }
}

關鍵點

• 使用 ref string outstr 實現輸出參數
• SqlContext.Pipe.Send 可輸出 SqlDataReader、SqlDataRecord 或字串
• SqlContext.Pipe.ExecuteAndSend 直接執行命令並送回結果

呼叫與測試

DECLARE @name nvarchar(4000), @outstr nvarchar(4000);
SET @name = 'david fan';
EXECUTE [TestProject].[dbo].[TestStoredProcedure] @name, @outstr OUTPUT;
PRINT @outstr;

二、表值函數 (TVF)

應用場景：搜尋目錄下特定類型檔案

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.IO;
using System.Security.Principal;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName = "BuildRow",
        TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")]
    public static IEnumerable FileListCs(string directoryName, string pattern)
    {
        FileInfo[] files;
        WindowsImpersonationContext OriginalContext = SqlContext.WindowsIdentity.Impersonate();
        try
        {
            DirectoryInfo di = new DirectoryInfo(directoryName);
            files = di.GetFiles(pattern);
        }
        finally
        {
            if (OriginalContext != null) OriginalContext.Undo();
        }
        return files;
    }

    public static void BuildRow(object Obj,
        ref SqlString fileName,
        ref SqlInt64 fileLength,
        ref SqlDateTime fileModified)
    {
        if (Obj != null)
        {
            FileInfo file = (FileInfo)Obj;
            fileName = file.Name;
            fileLength = file.Length;
            fileModified = file.LastWriteTime;
        }
        else
        {
            fileName = SqlString.Null;
            fileLength = SqlInt64.Null;
            fileModified = SqlDateTime.Null;
        }
    }
}

關鍵屬性

• FillRowMethodName：將每個集合元素轉換為資料列的方法
• TableDefinition：定義回傳資料表結構，需與 FillRow 參數對應
• 回傳型別須實作 IEnumerable（如陣列、List）

權限設定與部署

因需存取檔案系統（外部資源），專案屬性 → 資料庫 → 權限等級須設為 EXTERNAL_ACCESS，並執行：

ALTER DATABASE TestProject SET TRUSTWORTHY ON;

呼叫與結果

SELECT * FROM [TestProject].[dbo].[FileListCs] ('c:', '*.txt');

三、純量函數

回傳型別限制

必須為 SQL Server 純量型別（如 SqlString、SqlInt32 等），不可為 CLR 原生型別。

簡易範例

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ScalarFunction()
    {
        return new SqlString("Hello");
    }
}

呼叫與結果

SELECT [TestProject].[dbo].[ScalarFunction] ();

總結比較表