Using multiple rows from multiple tables for Persisted Computed Column with a Scalar UDF

Question

I'm trying to create a new field in the Order Transactions Table as a Persisted computed column using a Scalar UDF value as the value for the field.

I understand that a requirement for a Persisted column is that the value is deterministic, meaning that the multiple table UDF that I have is non-deterministic as it is not using fields from the source table.

Function:

USE [MyDatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[fnCalcOutstandingBalance]    
Script Date: 08/10/2018 14:01:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnCalcOutstandingBalance](@ItemReferance int)

RETURNS INT
WITH SCHEMABINDING 
AS
Begin
DECLARE @AcceptedQty INT
DECLARE @SumOfQty INT
DECLARE @Result INT

SELECT @AcceptedQty = 

    ISNULL([Accepted Quantity],0)
    FROM 
    dbo.[Order Transactions Table]
    WHERE @ItemReferance = [Item Referance] 

SELECT @SumOfQty =
    ISNULL(sum(Quantity),0)
    FROM dbo.[Delivery Table]
    GROUP BY [Item Referance]
    HAVING @ItemReferance = [Item Referance]

    SET @Result = ISNULL(@AcceptedQty,0) - ISNULL(@SumOfQty,0)

return @Result

END

I am looking for a workaround to be able to use the value that is generated from the above function within the Order Transactions Table.

Adding the column:

ALTER TABLE [Order Transactions Table]
ADD CalcOB AS [dbo].[fnCalcOutstandingBalance]([Item Referance]) PERSISTED

I have tested this function and it works as a standalone function call in a select as it should. The problem is that I need this to be used in a computed column without being a virtual column.

Answer 1

For anyone who is interested, I have managed to find a workaround to this problem via the use of a Cursor (Thank you @gbn) to handle the calculations on the existing data and to populate a new field (CalculatedOB) with the corresponding calculated value.

I have used Triggers (on [Order Transactions Table].[Accepted Quantity] and [Delivery Table].[Quantity]) to handle any future changes to the outstanding balance.

Both Cursor and all of the Triggers use the fnCalcOutstandingBalance() function to work out the values.

Cursor to populate existing data:

declare @refid int;
declare @Result int;
declare refcursor cursor for
select [Item Referance] from [Order Transactions Table];

open refcursor

fetch next from refcursor into @refid

while @@FETCH_STATUS = 0
begin 
print @refid

fetch next from refcursor into @refid
set @Result = [dbo].[fnCalcOutstandingBalance](@refid)

update [Order Transactions Table] set CalculateOB = @Result 
    where [Item Referance] = @refid
end 

close refcursor;
deallocate refcursor;

Update Trigger Example:

CREATE TRIGGER [dbo].[UPDATE_AcceptedQty]
ON [dbo].[Order Transactions Table]
for update
AS

DECLARE @ItemRef int;
declare @result int;

IF UPDATE ([Accepted Quantity])
Begin

SELECT @ItemRef=i.[Item Referance] from INSERTED i;

SET @result = [dbo].[fnCalcOutstandingBalance](@ItemRef)

UPDATE [Order Transactions Table] set CalculateOB = @Result 
    where [Item Referance] = @ItemRef

END

GO

The combination of these two techniques allowed me to mimic the functionality of a Computed column without the constraints of determinism requirements or a performance hit.

Big Thanks to @gbn and @Alan Burstein for their contributions!