Insert into table within function-CodePudding

I would like to insert the @OBV's value into a table inside the function. What is the correct way to achieve it?

alter function CalculateOnBalanceVolume (
    @operation varchar(3),
    @volume money
)
returns char(4) as
begin
    declare @prevOBV as money,
            @OBV as money
    set @prevOBV = (
    select top 1 OnBalanceVolume
    from OnBalanceVolume
    order by EventTime desc
    )
    if (@operation = 'add') set @OBV = @prevOBV   @volume
    if (@operation = 'sub') set @OBV = @prevOBV - @volume

    
    insert into OBVTable values (@OBV) // error
    


    return @OBV 
end;

CodePudding user response：

Functions cannot perform any actions known as side-effecting which includes inserting or updating or deleting from tables, so you cannot use a Function for this.

To use a stored procedure you might have:

create procedure CalculateOnBalanceVolume 
@operation varchar(3),
@volume decimal(9,2),
@OBV decimal(9,2) output
as

select top (1) @Obv=OnBalanceVolume   
    case when @Operation='add' then @volume else -@volume end
from OnBalanceVolume
order by EventTime desc

insert into OBVTable values (@OBV)

go

And then to invoke the procedure and get your output value you would do for example:

declare @OBV decimal(9,2)
exec CalculateOnBalanceVolume 'add', 100, @OBV output

select @OBV as OutputValue