I just join new organization. They like to put all the logic in stored procedure? May I know is it a good practice in Software Development.
In addition, I found some of stored procedure having some performance issue when increase number of user. For example, it takes some time to run the following stored procedure.
Next, it it good to have multiples join in a stored procedure like following.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_230_GetMRPByBatch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_230_GetMRPByBatch]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_230_GetMRPByBatch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[USP_230_GetMRPByBatch] AS'
END
GO
ALTER PROCEDURE [dbo].[USP_230_GetMRPByBatch]
@DIST_CD NVARCHAR(20),
@CUST_CD NVARCHAR(20),
@WHS_CD NVARCHAR(20),
@PRD_CD NVARCHAR(20),
@BILL_METHOD CHAR(1),
@INV_KEY NVARCHAR(50) = ''
AS
--2017-06-30 Chuah - Add Available stock summary from INVT_MASTER
--2018-01-02 Chuah - Add parameter @INV_KEY, need to consider allocated stock for invoice.
BEGIN
SET NOCOUNT ON;
DECLARE @COUNTRY_DT DATETIME
SET @COUNTRY_DT = dbo.GetCountryDate()
IF OBJECT_ID('tempdb..#BATCH_LOTEXP_PERIOD') IS NOT NULL
DROP TABLE #BATCH_LOTEXP_PERIOD
-- Get Batch Exclusion Expiry period
SELECT @PRD_CD AS PRD_CD, E.LOTEXP_IND, E.LOTEXP_MTH,
CASE B.REF_VALUE3 WHEN 0 THEN 0
ELSE ISNULL(J.LOTEXP_DAY,0) END 'LOTEXP_DAY'
INTO #BATCH_LOTEXP_PERIOD
FROM MST_CUSTOPTION AS E
INNER JOIN SET_REF B ON B.DIST_CD = @DIST_CD AND B.REF_TYPE = 'G_WAREHOUSE' AND B.REF_PARAM = @WHS_CD AND B.REF_VALUE2 = 'Y' AND B.REF_VALUE3 = '1'
OUTER APPLY (
SELECT TOP 1 C.LOTEXP_DAY
FROM MST_LOTEXP C
INNER JOIN MST_CUSTATTR D ON D.CUST_CD = @CUST_CD AND D.SYNCOPERATION <> 'D'
INNER JOIN VW_PRD I ON I.PRDCAT_CD = @PRD_CD
WHERE C.CUSTHIER_LEVEL = D.CUSTHIER_LEVEL AND C.CUSTHIER_CD = D.CUSTHIER_CD
AND C.PRDCAT_LEVEL = I.PRDCAT_LEVEL AND C.PRDCAT_CD = I.PRDCAT_CD
AND C.SYNCOPERATION <> 'D'
ORDER BY CAST(C.PRDCAT_LEVEL AS INT) DESC
) AS J
WHERE E.CUST_CD = @CUST_CD AND E.SYNCOPERATION <> 'D'
GROUP BY E.LOTEXP_IND, E.LOTEXP_MTH, B.REF_VALUE3, J.LOTEXP_DAY
-- Get Batch Expiry date
IF OBJECT_ID('tempdb..#BATCH_EXPIRY') IS NOT NULL
DROP TABLE #BATCH_EXPIRY
SELECT A.PRD_CD,
CASE A.LOTEXP_IND WHEN 1 THEN (CASE A.LOTEXP_MTH WHEN 0
THEN CONVERT(VARCHAR(10), DATEADD(DAY,A.LOTEXP_DAY,CONVERT(VARCHAR(10),@COUNTRY_DT,120)),120)
ELSE CONVERT(VARCHAR(10),DATEADD(MONTH,A.LOTEXP_MTH,CONVERT(VARCHAR(10),@COUNTRY_DT,120)),120) END)
ELSE CONVERT(VARCHAR(10),DATEADD(DAY,A.LOTEXP_DAY,CONVERT(VARCHAR(10),@COUNTRY_DT,120)),120) END EXPIRY_DT
INTO #BATCH_EXPIRY
FROM #BATCH_LOTEXP_PERIOD AS A
-- Get MRP by batch
SELECT A.MRP, A.STK_AVAILABLE + ISNULL(F.PRD_QTY, 0) AS STK_AVAILABLE, A.AVAILABLE_STK_MAIN + ISNULL(F.PRD_QTY, 0) AVAILABLE_STK_MAIN FROM (
SELECT A.MRP, SUM(A.AVAILABLE_STK) AS STK_AVAILABLE, MAX(E.AVAILABLE_STK) AVAILABLE_STK_MAIN
FROM INVT_BATCH A
INNER JOIN INVT_BIN B ON B.DIST_CD = A.DIST_CD AND B.WHS_CD = A.WHS_CD AND B.BIN = A.BIN AND B.SYNCOPERATION <> 'D'
LEFT OUTER JOIN #BATCH_EXPIRY AS C ON C.PRD_CD = A.PRD_CD
LEFT OUTER JOIN MST_PRDMRP AS D ON D.DIST_CD = A.DIST_CD AND D.PRD_CD = A.PRD_CD AND D.MRP = A.MRP
LEFT OUTER JOIN (
SELECT DIST_CD, WHS_CD, PRD_CD, SUM(AVAILABLE_STK) AVAILABLE_STK FROM INVT_MASTER
WHERE DIST_CD = @DIST_CD AND PRD_CD = @PRD_CD AND WHS_CD = @WHS_CD
GROUP BY DIST_CD, WHS_CD, PRD_CD
) AS E ON E.DIST_CD = A.DIST_CD AND E.PRD_CD = A.PRD_CD AND E.WHS_CD = A.WHS_CD
WHERE A.DIST_CD = @DIST_CD
AND A.WHS_CD = @WHS_CD
and A.PRD_CD = @PRD_CD
AND CONVERT(VARCHAR(10),A.EXPIRY_DT,120) > ISNULL(C.EXPIRY_DT, CONVERT(VARCHAR(10),@COUNTRY_DT,120))
--AND A.AVAILABLE_STK > 0
AND ISNULL(A.ONHOLD_IND,0) = 0
AND A.SYNCOPERATION <> 'D'
AND ISNULL(A.MRP,0) <> 0
AND (@BILL_METHOD = 'A' OR (@BILL_METHOD = 'L' AND D.PRD_CD IS NOT NULL))
GROUP BY A.DIST_CD, A.WHS_CD, A.PRD_CD, A.MRP
) A
LEFT JOIN (
SELECT TAXABLE_PRC, SUM(PRD_QTY * B.CONV_FACTOR_SML) PRD_QTY FROM TXN_INVDTL_BIN A
INNER JOIN MST_PRDUOM B ON A.PRD_CD = B.PRD_CD AND A.UOM_CD = B.UOM_CD AND B.SYNCOPERATION <> 'D'
WHERE INV_KEY = @INV_KEY AND A.PRD_CD = @PRD_CD AND WHS_CD = @WHS_CD
GROUP BY TAXABLE_PRC
) F ON F.TAXABLE_PRC = A.MRP
WHERE A.STK_AVAILABLE + ISNULL(F.PRD_QTY, 0) > 0
DROP TABLE #BATCH_LOTEXP_PERIOD
DROP TABLE #BATCH_EXPIRY
END
GO
