Oracle转SQL Server迁移：如何在SQL Server函数中实现DML操作？

核心结论 #

SQL Server中普通的T-SQL用户定义函数（标量/表值）不允许执行INSERT、DELETE等DML操作，这是SQL Server语言本身的限制，和SSMA配套支持包无关，不需要额外安装任何支持包。

可行解决方案 #

针对你遇到的“存储过程无法被SELECT调用”的问题，提供以下几种替代方案：

• 方案1：使用OPENQUERY调用带输出参数的存储过程
  先创建包含DML逻辑和输出参数的存储过程：

  CREATE PROCEDURE Proc_WithDML_AndOutput
      @ReturnVal INT OUTPUT
  AS
  BEGIN
      SET NOCOUNT ON;
      -- 执行DML操作
      INSERT INTO TargetTable (Column1) VALUES ('MigratedData');
      DELETE FROM TempTable WHERE Expired = 1;
      -- 设置返回结果
      SET @ReturnVal = (SELECT COUNT(*) FROM TargetTable);
  END
  

  配置本地服务器链接后，即可通过OPENQUERY在SELECT语句中调用：

  -- 先创建本地链接（仅需执行一次）
  EXEC sp_addlinkedserver @server = N'LOCAL_SERVER', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'localhost'
  
  -- 在SELECT中调用存储过程
  SELECT * 
  FROM OPENQUERY(LOCAL_SERVER, 
      'DECLARE @Result INT; 
       EXEC Proc_WithDML_AndOutput @Result OUTPUT; 
       SELECT @Result AS RecordCount;'
  )
  

• 方案2：使用CLR用户定义函数（需启用CLR集成）
  如果你的SQL Server环境允许启用CLR集成，可以编写CLR函数来执行DML操作。步骤大致为：

  1. 在.NET中编写包含DML逻辑的类库，将函数标记为[SqlFunction(DataAccess = DataAccessKind.ReadWrite)]
  2. 将编译后的DLL部署到SQL Server
  3. 创建对应的CLR函数，即可在SELECT语句中直接调用

• 方案3：拆分业务逻辑
  如果业务流程允许，可将DML操作和查询逻辑分离：先执行存储过程完成数据修改，再单独执行SELECT语句获取结果。这种方式最简洁，也符合SQL Server的最佳实践。

内容的提问来源于stack exchange，提问作者manjunath kurabar