Function

Question

I want to make sql function if any user issued any quantity that should be reduced from Balance column or if user returned any quantity that should also add into Balance Quantity.

And 

If User is entering first time qunatity into Total_qty column into ACTesting table then same quantity must show in Balance column

create table ACTesting

(

id int identity(1,1),

item nvarchar(max)  not null,

Issued nvarchar(max) null,

Used nvarchar(max) null,

Returned nvarchar(max) null,

Balance as(dbo.Issued(issued)),

Total_qty nvarchar(max)

)

 

 

alter function dbo.Issued(@issued nvarchar)

returns int

as

      Begin

      declare @Issued_Qty int = cast(@issued as int)

      declare @Amount23 int

      if not exists(select * from ACTesting)

       

                  begin

                        declare @Bal_Qty int=cast((Select Balance from

                         ACTesting where id =(select max(id) from 

                        ACTesting)) AS int)                  

                        set @Amount23=@Bal_Qty-@issued

                        return @Amount23                               

                  End        

            else

                  Begin

                        declare @Total_Qty int=cast((Select Total_qty from

                        ACTesting where id =(select max(id) from

                        ACTesting)) AS int)                  

                        set @Amount23=@Total_Qty

                        return @Amount23

                  End

                  return @Amount23             

      End