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.
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