kindly help.i have the following sp bt it take almost a minute to complete execution.any ideas will be accepted.checked on the net and applied what others r saying bt alas! no improvement....
USE [xxx]
GO
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Reports.Stock.StockSummary]
(
@StartDate as date,
@EndDate as date,
@DistributorUserID varchar(50)
)
as
declare @sqlStatement as nvarchar(max);
declare @sqlCondition as nvarchar (max);
set @sqlStatement ='';
set @sqlCondition ='';
set @sqlCondition = '';
SELECT DISTINCT Products.ProductCode, Products.Description As ProductName,
dbo.[GetOpeningStock](Products.ProductID,@StartDate,@DistributorUserID) OpeningStock,
dbo.GetIssuesBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As Issues,
dbo.GetAdjustmentBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As Adjustment,
dbo.GetOpeningStock(Products.ProductID,@StartDate,@DistributorUserID)
+ dbo.GetIssuesBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID)
+ dbo.GetAdjustmentBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As IssuesTotal,
dbo.GetSaleQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As Sales,
dbo.GetFocQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As FOC,
0 as Transfers, 0 as Replacement,
dbo.GetSaleQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) + dbo.GetFocQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) As TotalDisposal,
(dbo.GetOpeningStock(Products.ProductID,@StartDate,@DistributorUserID)
+ dbo.GetIssuesBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID)
+ dbo.GetAdjustmentBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID)-
dbo.GetSaleQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID) +
dbo.GetFocQuantityBySalesMan(@StartDate,@EndDate,@DistributorUserID,Products.ProductID)) as CurrentStock
FROM Products join StockHistory on StockHistory.ProductCode = Products.ProductCode
WHERE StockHistory.OpeningStock > 0
AND StockHistory.DistributorUserID = @DistributorUserID