SQL Server中如何创建可关联查询的带数据操作的数据库对象？

问题描述 #

SQL Server的标量函数不允许执行UPDATE/INSERT等带副作用的数据操作，而存储过程又无法直接与SELECT查询结果关联。需求是实现一个可从计数器表CounterValues获取并更新新值的数据库对象，且能在SELECT语句中调用（例如批量插入时生成IdBill），同时需兼容现有遗留系统结构，不能改用序列或Identity列。

尝试的标量函数逻辑如下（因存在副作用无法创建）：

create function getNewCounterValue(@Counter varchar(100))
returns int
as
begin
  declare @Value int

  select @Value = Value
  from CounterValues
  where Counter = @Counter

  set @Value = coalesce(@Value, 0) + 1

  update CounterValues set Value = @Value
  where Counter = @Counter

  if @@rowcount = 0 begin
    insert into CounterValues (Counter, Value) values (@Counter, @Value)
  end

  return @Value
end

期望的调用场景：

declare @CopyFrom date = '2022-07-01'
declare @CopyTo date = '2022-08-01'

insert into Bills (IdBill, Date, Provider, Amount)
       select getNewCounterValue('BILL'), @CopyTo, Amount
       from Bills
       where Date = @CopyFrom

可行方案 #

1. 触发器实现自动计数（固定表场景） #

针对特定表的插入操作，创建INSTEAD OF INSERT触发器，自动完成计数器的更新与数据插入，无需在查询中显式调用函数。

CREATE TRIGGER trg_Bills_Insert_Counter
ON Bills
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NextValue INT;

    -- 原子更新计数器并获取新值
    UPDATE CounterValues
    SET @NextValue = Value + 1, Value = Value + 1
    WHERE Counter = 'BILL';

    -- 计数器不存在则初始化
    IF @@ROWCOUNT = 0
    BEGIN
        SET @NextValue = 1;
        INSERT INTO CounterValues (Counter, Value) VALUES ('BILL', @NextValue);
    END

    -- 插入目标数据
    INSERT INTO Bills (IdBill, Date, Provider, Amount)
    SELECT @NextValue, i.Date, i.Provider, i.Amount
    FROM inserted i;
END

调用方式：只需执行正常的插入语句，触发器自动处理计数器：

declare @CopyFrom date = '2022-07-01'
declare @CopyTo date = '2022-08-01'

insert into Bills (Date, Provider, Amount)
select @CopyTo, Provider, Amount
from Bills
where Date = @CopyFrom

2. 分步批量处理（通用批量场景） #

先将待处理数据存入临时表，批量更新计数器后，再关联生成连续ID插入目标表，避免逐行操作的性能问题。

declare @CopyFrom date = '2022-07-01'
declare @CopyTo date = '2022-08-01'

-- 步骤1：暂存待复制数据
SELECT Provider, Amount, NEWID() AS TempId
INTO #TempBills
FROM Bills
WHERE Date = @CopyFrom

-- 步骤2：获取计数器当前值
DECLARE @CurrentValue INT
SELECT @CurrentValue = COALESCE(Value, 0) FROM CounterValues WHERE Counter = 'BILL'

-- 步骤3：批量更新计数器
UPDATE CounterValues
SET Value = @CurrentValue + (SELECT COUNT(*) FROM #TempBills)
WHERE Counter = 'BILL'

-- 计数器不存在则初始化
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO CounterValues (Counter, Value) VALUES ('BILL', (SELECT COUNT(*) FROM #TempBills))
    SET @CurrentValue = 0
END

-- 步骤4：生成连续ID并插入目标表
INSERT INTO Bills (IdBill, Date, Provider, Amount)
SELECT @CurrentValue + ROW_NUMBER() OVER (ORDER BY TempId), @CopyTo, Provider, Amount
FROM #TempBills

DROP TABLE #TempBills

3. CLR用户定义函数（通用可查询场景） #

SQL Server允许CLR函数执行读写操作，可编写C#代码实现带副作用的逻辑，注册后即可像标量函数一样在SELECT中调用。

步骤1：编写CLR代码（C#）

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

public class CounterFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.ReadWrite)]
    public static SqlInt32 GetNewCounterValue(SqlString counterName)
    {
        if (counterName.IsNull) return SqlInt32.Null;

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"
                UPDATE CounterValues
                SET Value = Value + 1
                OUTPUT inserted.Value
                WHERE Counter = @Counter;

                IF @@ROWCOUNT = 0
                BEGIN
                    INSERT INTO CounterValues (Counter, Value)
                    OUTPUT inserted.Value
                    VALUES (@Counter, 1);
                END";
            cmd.Parameters.AddWithValue("@Counter", counterName.Value);
            return (SqlInt32)cmd.ExecuteScalar();
        }
    }
}

步骤2：注册CLR函数到SQL Server

-- 启用CLR集成
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- 创建程序集（替换为你的DLL路径）
CREATE ASSEMBLY CounterFunctions
FROM 'C:\YourPath\CounterFunctions.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

-- 创建CLR函数
CREATE FUNCTION getNewCounterValue(@Counter varchar(100))
RETURNS int
AS EXTERNAL NAME CounterFunctions.CounterFunctions.GetNewCounterValue;

调用方式：与最初期望的标量函数调用完全一致，支持在SELECT中使用。

4. 存储过程+OUTPUT子句（单条/小规模场景） #

创建带输出参数的存储过程获取计数器值，若需关联查询，可结合临时表或游标处理（适合小规模数据）。

CREATE PROCEDURE GetNewCounterValue
    @Counter varchar(100),
    @NewValue int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Temp TABLE (NewValue int);

    -- 原子更新并返回新值
    UPDATE CounterValues
    SET Value = Value + 1
    OUTPUT inserted.Value INTO @Temp
    WHERE Counter = @Counter;

    -- 初始化计数器（若不存在）
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO CounterValues (Counter, Value)
        OUTPUT inserted.Value INTO @Temp
        VALUES (@Counter, 1);
    END

    SELECT @NewValue = NewValue FROM @Temp;
END

调用示例：

DECLARE @NewId INT
EXEC GetNewCounterValue 'BILL', @NewId OUTPUT

INSERT INTO Bills (IdBill, Date, Provider, Amount)
VALUES (@NewId, '2022-08-01', 'ProviderA', 100.00)

内容的提问来源于stack exchange，提问作者Marc Guillot