Optimize sql query

Question

Hi everone

 

I had creating a billing application and needs to calculate the balance qty,

I am having 5 tables

1. stock
2. purchase
3. PurchaseReturn 
4. Sale
5. SalesReturn

I want the balanceQty (based on ItemCode, which is common) in stock table and for that I had created a scalar function and used it as formula of computed column, now the issue is the number of rows is getting huge (approx 5000 as of now) and it is taking much time to return the rows more than 2 minutes.

 

Function

ALTER FUNCTION [dbo].[GetClosingStock]
(
--Add the parameters for the function here
 @ItemCode nvarchar(max)
)
RETURNS decimal(18, 3)
AS
BEGIN

--Declare the return variable here
DECLARE @sQty decimal(18, 3), @pQty decimal(18, 3), @prQty decimal(18, 3), @saleQty decimal(18, 3), @srQty decimal(18, 3)

DECLARE @Result decimal(18, 3)
-- Add the T - SQL statements to compute the return value here

Select @sQty = (select  ISNULL(sum(qty), 0) from Stock where ItemCode = @ItemCode)
Select @pQty = (select ISNULL(sum(qty), 0) from Purchase where ItemCode = @ItemCode)
select @prQty = (select ISNULL(sum(qty), 0) from PurchaseReturn where ItemCode = @ItemCode)
select @saleQty = (select ISNULL(sum(qty), 0) from Sale where ItemCode = @ItemCode)
select @srQty = (select ISNULL(sum(qty), 0) from SalesReturn where ItemCode = @ItemCode)

select @Result = (@sQty + (@pQty - @prQty) - (@saleQty - @srQty))

-- Return the result of the function
   RETURN @Result
END;

 

 How can I optimize it to perform better or guide me to calculate the ClosingStockQty in a much better way.

 

Does StoreProcedure helps me out or anything

 

Thanks

Devendra