CODEWITHSUNDEEP
sql-servert-sqlsql-server-2017
Marc Guillot
Marc Guillot

Reputation: 6465

How to join a table with the result of some manipulation of data (be an stored procedure, function, ...)?

This is one of the quirks of SQL-Server that I find more puzzling : to not be able to manipulate data within a function (execute UPDATE or INSERT commands), or not be able to join to a query the result of an stored procedure.

I want to code an object that returns the newer value from a table of counters, and be able to use its result on selects.

Something like :

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

So then I would be able to run commands like :

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

But SQL-Server doesn't allow to create functions that changes its data (Invalid use of a side-effecting operator), so it forces me to write getNewCounterValue as an stored procedure, but then I can't execute and join it to a query.

Is there any way to have an object that manipulates data capable to join its result to a query ?.

PS: I know that I could use sequences to get new counter values without needing to change data, but I'm working on a huge legacy database that already uses counter tables, not sequences. So I cannot change that without breaking a zillion other things.

I also know that I could declare IdBill as an Identity column, so I wouldn't need to retrieve new counter values to insert rows, but again this is a huge legacy database that uses counter tables, not identity columns, so I cannot change the column types without breaking the system.

Besides, these counters are just an example of why being able to join on a query the result of some data manipulation would be very useful. I like to write a lot of logic on the database, so I would take advantage of it on plenty other situations.

A few years ago I saw a very dirty trick to do so executing the data manipulation instructions as openrawset calls within your function, but it was a seriously ugly hack. There still is no better way to achieve this ?.

Thank you.

Upvotes: 0

Views: 121

Answers (1)

allmhuran
allmhuran

Reputation: 4464

You're clearly aware that a function is for returning data, and you're aware of sequences, and identity columns, and you have given a completely reasonable explanation in your question as to why you can't use this in this case.

But as you also said, the question is a bit more general than just sequence/identity problems. There is an coherent idea of "some kind of construct that can change data, and whose output can be composed directly into a select".

There's no "object" that exactly fits that description. Asking "why doesn't language X have feature Y" leads to philosophical discussions with good answers already provided by Eric Lippert here and here

I think there are a few more concrete answers in this case though:

1) Guaranteed idempotency.

A select returns a set (bag, collection, however you want to think about it). Then there is an obvious expectation that any process that runs for the result of a select may run for multiple rows. If the process is not idempotent, then the state of the system when the select is complete might depend on the number of rows in the result. It's also possible that the execution of the modifying process might change the semantics of the select, or the next iteration of the process, which leads to situations like the Halloween Problem.

2) Plan Compilation

Related to (1) but not precisely the same. How can the query optimizer approach this functionalty? It must generate a plan "ahead of time", and that plan depends on stateful information. Yes, we get adaptive memory grants with 2019, but that's a trivial sort of "mid flight change", and even that took years before it was implemented (by which I mean that I believe Oracle has been able to do this for years, though I could be wrong, I'm no Oracle guy).

3) It's not actually beneficial in a lot of use cases

Take the use case of generating a sequence. Why not just iterate and execute a stored procedure? One answer might be "because I want to avoid imperative iteration, we should try to be set based and declarative". But as your hypothetical function demonstrates, it would still be imperative and iterative, it would just be "hidden" behind the select. I think - or let's say I have an intuition - that many cases where it seems like it might be nice to put a state-changing operating into a select fall into this basket. We wouldn't really be gaining anything.

4) There acutally is a way to do it! (but only in the most trivial case)

When I said "composed directly into a select" I didn't use the word "compose" on a whim. We do have composable 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 ...
*/

Of course, this isn't substantially different from insert exec in that you can't have a "naked" select, it has to be the source for an insert first. And you can't join to the dml output directly, you have to get the output and then do the join. But at least it gives you a way to avoid the "nested insert exec" problem.

Upvotes: 2

Related Questions