如何将表与某些数据操作的结果连接起来(可以是存储过程、函数等)

Question

这是SQL Server的一个怪癖，我觉得它更令人困惑：不能在函数中操作数据(执行UPDATE或INSERT命令)，或者不能联接到查询存储过程的结果。

我想对一个对象进行编码，该对象从计数器表中返回较新的值，并能够在选择时使用其结果。

类似于：

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

但是SQL Server不允许创建更改其数据的函数(无效使用副作用运算符)，因此它迫使我将getNewCounterValue作为存储过程编写，但随后我无法执行它并将其联接到查询。

有没有任何方法可以让一个处理数据的对象将其结果连接到查询？。

PS：我知道我可以使用序列来获得新的计数器值，而无需更改数据，但我正在开发一个巨大的遗留数据库，该数据库已经使用计数器表，而不是序列。因此，如果不打破无数其他东西，我就无法改变这一点。

我也知道我可以将IdBill声明为Identity列，所以我不需要检索新的计数器值来插入行，但这是一个巨大的遗留数据库，它使用计数器表，而不是标识列，所以在不破坏系统的情况下，我无法更改列类型。

此外，这些计数器只是一个例子，说明了为什么能够在查询中加入某些数据操作的结果会非常有用。我喜欢在数据库上写很多逻辑，所以我会在很多其他情况下利用它。

几年前，我看到了一个非常肮脏的技巧，在函数中执行openrawset调用的数据操作指令，但这是一个非常丑陋的黑客。仍然没有更好的方法来实现这一点？。

谢谢。

Answer 1

您清楚地知道函数是用于返回数据的，您也知道序列和标识列，并且您在问题中给出了一个完全合理的解释，说明为什么在这种情况下不能使用它。

但正如你所说，这个问题比序列/身份问题更普遍。有一个连贯的"；可以改变数据的某种构造，并且其输出可以直接组成select"；。

没有"；对象"；这完全符合这个描述。询问"；为什么语言X不具有特征Y"；引发哲学讨论，Eric Lippert在这里和这里已经提供了很好的答案

不过，我认为在这种情况下还有一些更具体的答案：

1)保证幂等性

select返回一个集合(包、集合，不管您想怎么想)。然后有一个明显的期望，即为选择结果运行的任何进程都可能为多行运行。如果进程不是幂等的，那么当选择完成时系统的状态可能取决于结果中的行数。修改过程的执行也可能会改变选择的语义，或者改变过程的下一次迭代，从而导致类似万圣节问题的情况。

2)计划编制

与(1)有关，但不完全相同。查询优化器如何实现这一功能？它必须生成一个计划"；"提前"；，这个计划依赖于有状态的信息。是的，我们在2019年获得了自适应记忆拨款，但这是一种微不足道的"；飞行途中改变"；，即便如此，它也花了年的时间才得以实现(我的意思是，我相信Oracle多年来一直能够做到这一点，尽管我可能错了，但我不是Oracle的人)。

3)在很多用例中，它实际上并不有益

以生成序列的用例为例。为什么不只是迭代并执行一个存储过程呢？一个答案可能是"；因为我想避免命令式迭代，所以我们应该尝试基于集合和声明性的"。但正如你的假设函数所证明的那样，它仍然是命令性的和迭代的，它只是"；隐藏的"；选择后面。我认为——或者说我有一种直觉——在很多情况下，把一个改变状态的操作变成一个选择可能会很好，这就落入了这个篮子里。我们真的不会有任何收获。

4)确实有办法做到这一点！(但仅在最琐碎的情况下)

当我说"；直接组成一个选择"；我没有用"；撰写"；一时兴起。我们确实有可组合DML:

create table T(i int primary key, c char);
declare @output table (i int, c char);
insert @output (i, c)
select dml.i, dml.c
from   (
insert t (i, c)
output inserted.i, inserted.c
values (1, 'a')
) dml
/*     OK, you can't add this
join   SomeOtherTable on ...
*/

当然，这与insert exec没有实质性的不同，因为你不能有一个"；裸的"；选择，它必须首先作为插入的源。并且不能直接联接到dml输出，必须先获得输出，然后再进行联接。但至少它给了你一种避免"；嵌套插入exec"；问题