如何解决SQL函数调用存储过程执行动态SQL报INSERT EXEC错误问题

该报错：

Invalid use of a side-effecting operator 'INSERT EXEC'

触发原因是SQL Server 对自定义函数的运行有严格限制，禁止在函数内部执行任何可能产生副作用的操作，INSERT EXEC 属于会修改临时表状态的操作，本身就在函数禁止操作范围内，同时函数也不支持调用包含动态SQL、数据修改逻辑的存储过程，这套实现逻辑本身不符合SQL Server的函数使用规范。

方案1：直接用存储过程实现全部逻辑（最推荐） #

放弃自定义函数的封装，直接用存储过程实现需求即可，存储过程本身就支持动态SQL，不需要绕函数调用的弯路。
另外你原有存储过程还存在两个问题：

• 直接拼接SQL存在严重的SQL注入风险
• 执行动态SQL的语法错误，exec @cmd 是调用存储过程的写法，执行动态SQL需要加括号，更推荐用sp_executesql做参数化处理

修正后的完整存储过程示例：

create procedure myProc
    @TableName varchar(100), 
    @FilterCol varchar(100), 
    @FilterValue varchar(100),
    @SumResult float output
as
begin
    -- 先校验表名和字段名是否合法，避免注入
    if not exists (select 1 from sys.tables where name = @TableName)
        or not exists (select 1 from sys.columns where name = @FilterCol and object_id = object_id(@TableName))
    begin
        raiserror('非法的表名或字段名',16,1)
        return
    end

    declare @cmd nvarchar(max) = N'select @Sum = sum(price) from ' + QUOTENAME(@TableName) + N' where ' + QUOTENAME(@FilterCol) + N' = @Val'
    exec sp_executesql @cmd, N'@Val varchar(100), @Sum float output', @Val = @FilterValue, @Sum = @SumResult output
end

调用方式：

declare @res float
exec myProc '你的业务表名', '过滤字段名', '过滤值', @res output
select @res

方案2：固定枚举场景下用静态函数实现 #

如果你要查询的表名和过滤字段是有限的固定枚举值，可以不用动态SQL，直接写静态标量函数，避开动态SQL的限制：

create function myFunc 
    (@var1 varchar(100), 
     @var2 varchar(100), 
     @var3 varchar(100))
returns float
as
begin
    return (
        case @var1
            when '表1' then (select sum(price) from 表1 where case @var2 when '字段1' then 字段1 when '字段2' then 字段2 end = @var3)
            when '表2' then (select sum(price) from 表2 where case @var2 when '字段1' then 字段1 when '字段2' then 字段2 end = @var3)
            -- 其他枚举逻辑自行补充
        end
    )
end

方案3：CLR标量函数（不推荐） #

如果必须要在查询语句中调用类似函数的逻辑，可以开发CLR标量函数实现动态SQL查询，但需要服务器开启CLR集成权限，对生产环境的安全配置要求较高，没有特殊需求不建议使用。

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