Stored proc for errorhandling understanding

/*---------------------------------------------------------------------------
stored Procedure name  :  USP_Load_PL_Tool
created by             :  Mattel  babji
craeted date           :  06/21/2017
Description            :  Loading ToolData Staging data(ToolData_Agile) to persistant table(PL_INTG_BOMData)
                           as per TDSL rules
parameters             :   @batchdate: we should pass datime as ssis package start time

----------------------------------------------------------------------------*/
--USE DMAX
--GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'USP_Load_PL_Tool')
DROP PROCEDURE USP_Load_PL_Tool
GO

CREATE PROCEDURE dbo.USP_Load_PL_Tool
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(4000)=''
DECLARE @mincnt int
DECLARE @maxcnt int
DECLARE @ToolNumber VARCHAR(50)=''

IF OBJECT_ID('tempdb..#Tooldata') IS NOT NULL
  DROP TABLE #Tooldata

CREATE TABLE #Tooldata
(   id INT IDENTITY(1,1),
[ToolNumber] varchar(50) NOT NULL,
[ToolDescription] varchar(120) NULL,
[ToolType] varchar(50) NULL,
[ToolClass] varchar(5) NULL,
[ToolLocation] varchar(100) NULL,
[ToolCavity] varchar(10) NULL,
[MfrName] varchar(255) NULL,
[MfrItemType] varchar(255) NULL,
[ToolStatus] varchar(10) NULL,
[ToolCapacity] decimal(9, 2) NULL,
[LatestCost] decimal(11, 2) NULL,
[LatestCostLastUsedDate] date NULL,
[TotalPartsPerTool] int NULL,
[CumulativeShot] bigint NULL,
[DataSource] varchar(10) NULL,
[isProcessed] varchar(1) NULL,
[MAXUpdateTime] datetime NULL
)

/*This stored proc will update the what the item's is using only one product then update PL_Tool table column isprocessed to 'D'
UI team will those item values isprocessed values to 'R',Then every run we should delete the isprocessed value 'R' from PL_Tool */

DELETE
FROM dbo.PL_Tool
WHERE isProcessed='R'

/*Check all Agile(PL_INTG_ToolAgile) ItemNumber exist in  the PL_Item table
,If Exist,leave
,If Does not exist,Insert ItemNumber data into PL_Item from PL_INTG_ToolAgile */

EXEC dbo.USP_Load_PL_Item_From_PersistentTbl @ItemType='ToolAgile'

INSERT INTO #Tooldata
(
ToolNumber,ToolDescription,ToolType,ToolClass,ToolLocation,ToolCavity,MfrName,MfrItemType,
ToolStatus,ToolCapacity,LatestCost,TotalPartsPerTool,CumulativeShot
)
SELECT
ToolNumber
,ISNULL(ToolDescription,'') AS ToolDescription
,ISNULL(ToolType,'') AS ToolType
,ISNULL(ToolClass,'') AS ToolClass
,ISNULL(ToolLocation,'') AS ToolLocation
,ISNULL(ToolCavity,'') AS ToolCavity
,ISNULL(MfrName,'') AS MfrName
,ISNULL(MfrItemType,'') AS MfrItemType
,'' AS ToolStatus
,0.00  AS ToolCapacity
,0.00 AS LatestCost
,0 AS TotalPartsPerTool
,0 AS CumulativeShot
FROM
(SELECT DISTINCT
 ToolNumber
,ToolDescription
,ToolType
,MfrName
,MfrItemType
,ToolCavity
,ToolClass
,ToolLocation
,ROW_NUMBER() OVER(PARTITION BY ToolNumber ORDER BY  t1.updatedtime DESC) As rownum
 FROM PL_INTG_ToolAgile t1 WITH(NOLOCK)
 INNER JOIN ProductData t2 WITH(NOLOCK)
 ON t1.ProductNumber=t2.ProductNumber
 INNER JOIN dbo.Pl_ProductDash t3
 ON t2.ProductNumber=t3.ProductNumber
 INNER JOIN PL_Item t4 WITH(NOLOCK)
 ON t1.ItemNumber=t4.AutoGenNbr
 --AND t1.ItemNumber=t4.ItemNumber
 WHERE t1.ToProcessToolAgile='Y'
 and t1.ToolNumber<>''
 --AND ToolNumber='00315-A501-01'
 ) AS ToolAgile WHERE rownum=1


SELECT @maxcnt=ISNULL(MAX(id),0) FROM #Tooldata
SET @mincnt=1
/* inser and upadte the PL_Tool table from PL_INTG_ToolAgile  with ToProcessToolAgile as "Y"  data*/
WHILE(@mincnt<=@maxcnt)
BEGIN
BEGIN TRY
SET @ToolNumber=''
SET @ErrorMessage=''
SELECT @ToolNumber=ToolNumber FROM #Tooldata where id=@mincnt

;MERGE dbo.[PL_Tool] AS T
USING
(
SELECT
 [ToolNumber]
,[ToolDescription]
,[ToolType]
,[MfrName]
,[MfrItemType]
,[ToolCavity]
,[ToolClass]
,[ToolLocation]
,[ToolStatus]
,[ToolCapacity]
,[LatestCost]
,[TotalPartsPerTool]
,[CumulativeShot]
,'AGILE' AS [DataSource]
,'N' AS [isProcessed]
,CURRENT_TIMESTAMP AS [MAXUpdateTime]
FROM #Tooldata
WHERE ToolNumber=@ToolNumber
) AS S
ON T.ToolNumber=S.ToolNumber

WHEN MATCHED THEN --AND T.DataSource='AGILE'
UPDATE
SET   T.[ToolType]=CASE WHEN T.DataSource='AGILE' THEN  S.[ToolType] ELSE T.[ToolType] END,
      T.[ToolClass]=CASE WHEN T.DataSource='AGILE' THEN  S.[ToolClass] ELSE  T.[ToolClass] END,
 T.[ToolDescription]=CASE WHEN T.DataSource='AGILE' THEN S.[ToolDescription] ELSE T.[ToolDescription] END,
 T.[MfrName]=CASE WHEN T.DataSource='AGILE' THEN S.[MfrName] ELSE T.[MfrName] END,
 T.[MfrItemType]=CASE WHEN T.DataSource='AGILE' THEN S.[MfrItemType] ELSE T.[MfrItemType] END ,

 T.[ToolLocation]= CASE WHEN T.[ToolLocation]='' AND T.DataSource='MIMICS' AND T.[CumulativeShot]=0 THEN S.[ToolLocation]
WHEN T.[ToolLocation]='' AND T.DataSource='AGILE'  THEN S.[ToolLocation]
                   ELSE T.[ToolLocation] END,

 T.[ToolCavity]= CASE WHEN T.[ToolCavity]='' AND T.[DataSource]='MIMICS' AND T.[CumulativeShot]=0 THEN S.[ToolCavity]
  WHEN T.[ToolCavity]='' AND T.[DataSource]='AGILE' THEN S.[ToolCavity]
                 ELSE  T.[ToolCavity] END,

      T.[ToolStatus]=ISNULL(T.[ToolStatus],S.[ToolStatus]),
 T.[ToolCapacity]=ISNULL(T.[ToolCapacity],0.00),
 T.LatestCost=ISNULL(T.[LatestCost],0.00),
 T.TotalPartsPerTool=ISNULL(T.TotalPartsPerTool,0),
 T.[CumulativeShot]=ISNULL(T.[CumulativeShot],0),
      T.[isProcessed]=S.[isProcessed],
 T.[MAXUpdateTime]=S.[MAXUpdateTime]
WHEN NOT MATCHED THEN
INSERT
(
 [ToolNumber]
,[ToolDescription]
,[ToolType]
,[ToolClass]
,[ToolLocation]
,[ToolCavity]
,[MfrName]
,[MfrItemType]
,[DataSource]
,[ToolStatus]
,[ToolCapacity]
,[LatestCost]
,[TotalPartsPerTool]
,[CumulativeShot]
,[isProcessed]
,[MAXUpdateTime]
)
VALUES
(
 S.[ToolNumber]
,S.[ToolDescription]
,S.[ToolType]
,S.[ToolClass]
,S.[ToolLocation]
,S.[ToolCavity]
,S.[MfrName]
,S.[MfrItemType]
,S.[DataSource]
,S.[ToolStatus]
,S.[ToolCapacity]
,S.[LatestCost]
,S.[TotalPartsPerTool]
,S.[CumulativeShot]
,S.[isProcessed]
,S.[MAXUpdateTime]
);
END TRY
BEGIN CATCH
SET @ErrorMessage =@ErrorMessage+'Sp_Name:USP_Load_PL_Tool getting Error ToolNumber is:'+ISNULL(CAST(@ToolNumber AS NVARCHAR(100)),'')+ERROR_MESSAGE();
INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT
'USP_Load_PL_Tool' AS [SPName]
,'' As [ItemNumber]
,'' AS [AutoGenNbr]
,'' AS [ProductNumber]
,'' As [dashCode]
,@ErrorMessage AS [Comments]
,GETDATE() As [Todays_date]          

END CATCH
SET @mincnt=@mincnt+1
END

/* After insert/update the   PL_Tool Agile data then check PL_Tool combination data (ProductNumber,ToolNumber,AutoGenNbr) exist or not exist in PL_ProdItemToolRel
if exist leave as it as,if does not exist insert into PL_ProdItemToolRel table */
MERGE PL_ProdItemToolRel AS T
USING (
SELECT
[ProductNumber],
[ToolNumber],
[AutoGenNbr]
FROM
(
SELECT DISTINCT
 t1.[ProductNumber],
 t1.[ToolNumber],
 t4.[AutoGenNbr],
 ROW_NUMBER() OVER(PARTITION BY t1.[ProductNumber],t1.[ToolNumber], t3.[AutoGenNbr] order by  t3.[AutoGenNbr]) as rownum
FROM dbo.PL_INTG_ToolAgile AS t1 WITH(NOLOCK)
INNER HASH JOIN (SELECT DISTINCT ProductNumber FROM dbo.ProductData) AS t2
ON t1.ProductNumber=t2.ProductNumber
INNER JOIN PL_ProductDash t3 WITH(NOLOCK)
ON  t3.ProductNumber=t2.ProductNumber
INNER JOIN dbo.PL_ITEM AS t4 WITH(NOLOCK)
ON t1.ItemNumber=t4.AutoGenNbr
--AND t3.ItemNumber=t4.ItemNumber
--AND t3.AutoGenNbr=t4.AutoGenNbr
INNER JOIN PL_Tool as t5 WITH(NOLOCK)
ON t1.ToolNumber=t5.ToolNumber
WHERE t1.ToProcessToolAgile='Y'
AND t5.isProcessed='N'
--t4.[isProcessed]='N'


) As tool where rownum=1
) AS S
ON T.[ProductNumber]=S.[ProductNumber]
AND T.[ToolNumber]=S.[ToolNumber]
AND T.[ItemAutoGenNbr]=S.[AutoGenNbr]
WHEN NOT MATCHED THEN
INSERT
(
[ProductNumber],
[ToolNumber],
[ItemAutoGenNbr]
)
VALUES
(
S.[ProductNumber],
S.[ToolNumber],
S.[AutoGenNbr]
);


 /* Update the processed ToolNUmber's in agile persistent table (PL_INTG_ToolAgile) column ToProcessToolAgile to 'N'*/
 UPDATE t1
 SET ToProcessToolAgile='N'
 FROM dbo.PL_INTG_ToolAgile t1 WITH(ROWLOCK)
 INNER JOIN ProductData t2
 ON t1.ProductNumber=t2.ProductNumber
 INNER JOIN PL_ProductDash t3
 ON t2.ProductNumber=t3.ProductNumber
 AND t1.ToProcessToolAgile='Y'
 INNER JOIN dbo.[PL_Tool] t4 WITH(NOLOCK)
 ON t1.ToolNumber=t4.ToolNumber
 --AND t4.isProcessed='N'

 /* Agile ProductNumber ErrorLog */
 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'AGILE ProductData Check: ProductNumber ('+[ProductNumber]+') Does Not Exist In ProductData Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolAgile WITH(NOLOCK)
WHERE  ProductNumber NOT IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND  ToProcessToolAgile='Y'


--- Product Dash Error Log
 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'AGILE ProductDash Check: ProductNumber ('+[ProductNumber]+' ) Exist In ProductData And Does Not exist in PL_ProductDash Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolAgile WITH(NOLOCK)
WHERE  ProductNumber IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND ProductNumber NOT IN( SELECT DISTINCT ProductNumber FROM PL_ProductDash)
AND  ToProcessToolAgile='Y'



------------------------------------------------------------------------------------------------------------------------------------

/*Check all MIMICS ItemNumber exist in  the PL_Item table
,If Exist,leave
,If Does not exist,Insert ItemNumber data into PL_Item from PL_INTG_ToolMIMCS */

EXEC USP_Load_PL_Item_From_PersistentTbl @ItemType='ToolMIMICS'

-->cleanup the agile data from temp table
TRUNCATE TABLE #Tooldata

INSERT INTO #Tooldata
(
ToolNumber,ToolDescription,ToolType,ToolClass,ToolLocation,ToolCavity,MfrName,MfrItemType,
ToolStatus,ToolCapacity,LatestCost,[LatestCostLastUsedDate],TotalPartsPerTool,CumulativeShot
)
SELECT
[ToolNumber]
,'' AS ToolDescription
,'' As ToolType
,'' As ToolClass
,ISNULL([ToolLocation],'') AS [ToolLocation]
,ISNULL([ToolCavity],'') AS [ToolCavity]
,'' AS MfrName
,'' AS MfrItemType
,ISNULL([ToolStatus],'') AS [ToolStatus]
,isnull([ToolCapacity],0.00) as [ToolCapacity]
,isnull([LatestCost],0.00) as [LatestCost]
,[LatestCostLastUsedDate]
,isnull([TotalPartsPerTool],0) as [TotalPartsPerTool]
,isnull([CumulativeShot],0) as [CumulativeShot]
FROM (
SELECT DISTINCT
 [ToolNumber]
,[ToolStatus]
,[ToolLocation]
,[ToolCavity]
,[TotalPartsPerTool]
,[ToolCapacity]
,[CumulativeShot]
,[LatestCost]
,[LatestCostLastUsedDate]
,T1.UpdatedTime
,ROW_NUMBER() OVER(PARTITION BY ToolNumber ORDER BY  t1.updatedtime desc) As rownum
 FROM PL_INTG_ToolMIMICS t1 WITH(NOLOCK)
 INNER HASH JOIN Productdata AS t2
 ON t1.ProductNumber=t2.ProductNumber
 AND t1.ToProcessToolMIMICS='Y'
 INNER JOIN dbo.Pl_ProductDash t3 WITH(NOLOCK)
 ON t2.ProductNumber=t3.ProductNumber
 INNER JOIN PL_Item t4 WITH(NOLOCK)
 ON t1.ItemNumber=t4.AutoGenNbr
 WHERE t1.ToolNumber<>''
) AS ToolMIMICS WHERE RowNum=1
--AND ToolNumber='00315-B511-01'


--select * from PL_INTG_ToolMIMICS where ToolNumber='00315-B511-01'
SELECT @maxcnt=ISNULL(MAX(id),0) FROM #Tooldata
SET @mincnt=1
/* INser and upadte the PL_Tool table from PL_INTG_ToolMIMCS with ToProcessToolAgile as "Y"  data*/
WHILE(@mincnt<=@maxcnt)
BEGIN
BEGIN TRY
SET @ToolNumber=''
SET @ErrorMessage=''
SELECT @ToolNumber=ToolNumber from #Tooldata where id=@mincnt
;MERGE dbo.[PL_Tool] As T
USING
(
SELECT DISTINCT
  [ToolNumber]
 ,[ToolStatus]
 ,[ToolLocation]
 ,[ToolCavity]
 ,[TotalPartsPerTool]
 ,[ToolCapacity]
 ,[CumulativeShot]
 ,[LatestCost]
 ,[LatestCostLastUsedDate]
 ,[MfrName]
 ,[MfrItemType]
 ,[ToolClass]
 ,[ToolType]
 ,[ToolDescription]
,'MIMICS' AS [DataSource]
,'N' AS [isProcessed]
,CURRENT_TIMESTAMP AS [MAXUpdateTime]
FROM  #Tooldata
WHERE ToolNumber=@ToolNumber
) AS S
ON T.ToolNumber=S.ToolNumber
WHEN MATCHED THEN
UPDATE SET T.[ToolStatus]=S.[ToolStatus],
           T.[ToolLocation]= CASE WHEN  S.[CumulativeShot]>0 THEN S.[ToolLocation]
 WHEN  S.[CumulativeShot]=0 and (S.[ToolLocation] IS NOT NULL or S.[ToolLocation] != '') THEN S.[ToolLocation]
                         ELSE ISNULL(T.[ToolLocation],'')  END,
           T.[ToolCavity]= CASE WHEN S.[CumulativeShot]>0 THEN S.[ToolCavity]
WHEN S.[CumulativeShot]=0 and (S.[ToolCavity]  IS NOT NULL or S.[ToolCavity]  != '') THEN S.[ToolCavity]
                       ELSE  ISNULL(T.[ToolCavity],'')  END,
           T.[TotalPartsPerTool]=S.[TotalPartsPerTool],
  T.[ToolCapacity]=S.[ToolCapacity],
  T.[CumulativeShot]=S.[CumulativeShot],
  T.[LatestCost]=S.[LatestCost],
  T.[LatestCostLastUsedDate]=S.[LatestCostLastUsedDate],
  T.[MfrName]=ISNULL(T.[MfrName],S.[MfrName]),
  T.[MfrItemType]=ISNULL(T.[MfrItemType],S.[MfrItemType]),
  T.[ToolClass]=ISNULL(T.[ToolClass],S.[ToolClass]),
  T.[ToolType]=ISNULL(T.[ToolType],S.[ToolType]),
  T.[ToolDescription]=ISNULL(T.[ToolDescription],S.[ToolDescription]),
  T.[DataSource]=S.[DataSource],
  T.[isProcessed]=S.[isProcessed],
  T.[MAXUpdateTime]=S.[MAXUpdateTime]

WHEN NOT MATCHED THEN
INSERT
(
  [ToolNumber]
 ,[ToolStatus]
 ,[ToolLocation]
 ,[ToolCavity]
 ,[TotalPartsPerTool]
 ,[ToolCapacity]
 ,[CumulativeShot]
 ,[LatestCost]
 ,[LatestCostLastUsedDate]
 ,[MfrName]
 ,[MfrItemType]
 ,[ToolClass]
 ,[ToolType]
 ,[ToolDescription]
 ,[DataSource]
 ,[isProcessed]
 ,[MAXUpdateTime]
)
VALUES
(
  S.[ToolNumber]
 ,S.[ToolStatus]
 ,S.[ToolLocation]
 ,S.[ToolCavity]
 ,S.[TotalPartsPerTool]
 ,S.[ToolCapacity]
 ,S.[CumulativeShot]
 ,S.[LatestCost]
 ,S.[LatestCostLastUsedDate]
 ,S.[MfrName]
 ,S.[MfrItemType]
 ,S.[ToolClass]
 ,S.[ToolType]
 ,S.[ToolDescription]
 ,S.[DataSource]
 ,S.[isProcessed]
 ,S.[MAXUpdateTime]
);
END TRY
BEGIN CATCH
SET @ErrorMessage =@ErrorMessage+'Sp_Name:USP_Load_PL_Tool getting Error on MIMICS Tool is:'+ISNULL(CAST(@ToolNumber AS NVARCHAR(100)),'')+ERROR_MESSAGE();
INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT
'USP_Load_PL_Tool' AS [SPName]
,@ToolNumber As [ItemNumber]
,'' AS [AutoGenNbr]
,'' AS [ProductNumber]
,'' As [dashCode]
,@ErrorMessage AS [Comments]
,GETDATE() As [Todays_date]          

END CATCH
SET @mincnt=@mincnt+1
END

/* After insert/update the   PL_Tool MIMICS data then check PL_Tool combination data (ProductNumber,ToolNumber,AutoGenNbr) exist or not exist in PL_ProdItemToolRel
if exist leave as it as,if does not exist insert into PL_ProdItemToolRel table FROM PL_INTG_ToolMIMICS*/


MERGE dbo.PL_ProdItemToolRel AS T
USING (
SELECT
[ProductNumber],
[ToolNumber],
[AutoGenNbr]
FROM
(
SELECT DISTINCT
 t1.[ProductNumber],
 t1.[ToolNumber],
 t1.ItemNumber AS [AutoGenNbr],
 ROW_NUMBER() OVER(PARTITION BY t1.[ProductNumber],t1.[ToolNumber], t1.ItemNumber order by  t1.ItemNumber) as rownum
FROM dbo.PL_INTG_ToolMIMICS AS t1 WITH(NOLOCK)
INNER JOIN (SELECT DISTINCT ProductNumber FROM dbo.ProductData) AS t2
ON t1.ProductNumber=t2.ProductNumber
AND t1.ToProcessToolMIMICS='Y'
INNER JOIN PL_ProductDash t3 WITH(NOLOCK)
ON  t3.ProductNumber=t2.ProductNumber
INNER JOIN dbo.PL_ITEM AS t4 WITH(NOLOCK)
ON t1.ItemNumber=t4.AutoGenNbr
--AND t4.[isProcessed]='N'
INNER JOIN dbo.PL_Tool as t5 WITH(NOLOCK)
ON t1.ToolNumber=t5.ToolNumber
WHERE t5.isProcessed='N'
--AND t1.DeleteStatus<>'Y'
) As tool where rownum=1
) AS S
ON T.[ProductNumber]=S.[ProductNumber]
AND T.[ToolNumber]=S.[ToolNumber]
AND T.[ItemAutoGenNbr]=S.[AutoGenNbr]
WHEN NOT MATCHED THEN
INSERT
(
[ProductNumber],
[ToolNumber],
[ItemAutoGenNbr]
)
VALUES
(
S.[ProductNumber],
S.[ToolNumber],
S.[AutoGenNbr]
);


-------------------------------------------------------------------------------------------------------------------------------
-- • Select the records from PL_INTG_ToolMIMICS table where ToProcessToolMIMICS column is ‘Y’ AND DeleteStatus column is ‘Y’
--1. Lookup PL_ProdItemToolRel table for the combination, ProductNumber, AutoGenNbr and ToolNumber. Get the AutoGenNbr for the specific item from PL_Item table.
--If data exists delete the row.
--2. Check for the same Tool number exists for any other Product Item combination in PL_Tool table
-- If yes, leave as-is
-- If no,  mark the isProcessed column as ‘D’
--• Select the records from PL_Tool table where isProcessed column is ‘R’
--1. Delete the rows marked as ‘D’  in isProcessed column. This means MediaBeacon has removed the respective data, so we can delete the entry from PL_Tool table

;WITH CTE_Tooldata_Update As
(
SELECT t2.ToolNumber,count(*) As cnt
 FROM dbo.PL_INTG_ToolMIMICS t1 WITH(NOLOCK)
 INNER JOIN PL_ProdItemToolRel t2 WITH(NOLOCK)
 ON t1.ToolNUmber=t2.ToolNumber
 INNER JOIN PL_ITEM t3 WITH(NOLOCK)
 ON t2.ItemAutoGenNbr=t3.AutoGenNbr
 WHERE ToProcessToolMIMICS='Y'
 AND DeleteStatus='Y'
 GROUP BY t2.ToolNumber HAVING COUNT(*)=1
 )
 UPDATE t1
 SET isProcessed='D'
 FROM PL_Tool t1 WITH(ROWLOCK)
 JOIN  PL_ProdItemToolRel t2 WITH(NOLOCK)
 ON t1.ToolNumber=t2.ToolNumber
 INNER JOIN CTE_Tooldata_Update sub
 ON t2.ToolNumber=sub.ToolNumber


 --select * from PL_ProdItemToolRel where ToolNumber='FGG71-S509-01'

;WITH CTE_ToolRelation_Delete As
(
SELECT t2.ItemAutoGenNbr,count(*) As cnt
 FROM dbo.PL_INTG_ToolMIMICS t1 WITH(NOLOCK)
 INNER JOIN PL_ProdItemToolRel t2 WITH(NOLOCK)
 ON t1.ProductNumber=t2.ProductNumber
 AND t1.ItemNumber=t2.ItemAutoGenNbr
 AND t1.ToolNUmber=t2.ToolNumber
 INNER JOIN PL_ITEM t3 WITH(NOLOCK)
 ON t2.ItemAutoGenNbr=t3.AutoGenNbr
 WHERE t1.ToProcessToolMIMICS='Y'
 AND DeleteStatus='Y'
 GROUP BY t2.ItemAutoGenNbr HAVING COUNT(*)=1
 )
 DELETE main
 FROM PL_ProdItemToolRel main WITH(ROWLOCK)
 INNER JOIN CTE_ToolRelation_Delete sub
 ON main.ItemAutoGenNbr=sub.ItemAutoGenNbr



 /* Update the processed ToolNUmber's in MIMICS persistent table (PL_INTG_ToolMIMICS) column ToProcessToolAgile to 'N'*/

 UPDATE t1
 SET ToProcessToolMIMICS='N'
 FROM dbo.PL_INTG_ToolMIMICS t1 WITH(ROWLOCK)
 INNER JOIN ProductData t2 WITH(NOLOCK)
 ON t1.ProductNumber=t2.ProductNumber
 INNER JOIN PL_ProductDash t3 WITH(NOLOCK)
 ON t2.ProductNumber=t3.ProductNumber
 AND t1.ToProcessToolMIMICS='Y'
 INNER JOIN dbo.[PL_Tool] t4 WITH(NOLOCK)
 ON t1.ToolNumber=t4.ToolNumber
 --AND t4.isProcessed='N'


 /* MIMICS ProductNumber Error Log */

 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'MIMICS ProductData Check: ProductNumber ('+[ProductNumber]+') Does Not Exist In ProductData Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolMIMICS WITH(NOLOCK)
WHERE  ProductNumber NOT IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND  ToProcessToolMIMICS='Y'
--AND DeleteStatus<>'Y'

--- Product Dash Error Log
 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'MIMICS ProductDash Check: ProductNumber ('+[ProductNumber]+') Exist In ProductData And Does Not exist in PL_ProductDash Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolMIMICS WITH(NOLOCK)
WHERE  ProductNumber IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND ProductNumber NOT IN( SELECT DISTINCT ProductNumber FROM PL_ProductDash)
AND  ToProcessToolMIMICS='Y'


IF OBJECT_ID('tempdb..#Tooldata') IS NOT NULL
  DROP TABLE #Tooldata

END TRY
BEGIN CATCH
SET @ErrorMessage =@ErrorMessage+'Sp_Name:USP_Load_PL_Tool getting Error on:'+ERROR_MESSAGE();
INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT
'USP_Load_PL_Tool' AS [SPName]
,'' As [ItemNumber]
,'' AS [AutoGenNbr]
,'' AS [ProductNumber]
,'' As [dashCode]
,@ErrorMessage AS [Comments]
,GETDATE() As [Todays_date]          

END CATCH
SET NOCOUNT OFF;
END

How To Generate ROW_NUMBER with out Using Row_Number() function

;WITH cte AS
 ( select 2 as num
   union all
   select 3 as num
   union all
   select 2 as num
   union all
   select 3 as num
   union all
   select 4 as num
   union all
   select 5 as num
   union all
   select 2 as num
)
SELECT num
,Row_number() over(partition by num order by num ) as row_num
,sum(1) OVER( PARTITION BY num  ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as final --- row number replace
,sum(1) OVER( PARTITION BY num  ORDER BY num) as final1--> it will give count

FROM cte

ACID Properties In SQL Sever

      When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics. The developers of the components that comprise the transaction are assured that these characteristics are in place. They do not need to manage these characteristics themselves. These characteristics are known as the ACID properties. 

      If you are creating a database that only has one user who issues SQL statements one at a time,your database implementation would be quite simple. However, most DBMS's are the backend record storage for front-end applications that have multiple users modifying records at the same time. This presents several problems that ACID attempts to define and solve.





ACID stands for Atomicity pronounced "Atom-miss-city", Consistency, Isolation and Durability. Let's look at each one-by-one. 

Atomicity:
            Either all operations of the transaction are reflected in to the database or none.
             Atomicity is  pronounced "atom-miss-city". By "atomic" we mean "as a single atom or unit". This is a method of grouping data together as single unit so that the atomic unit of data is either entered into the database or not. It's either "All or nothing" as partially updating a record in the database can have bad results in real-life.

RollBack Truncate Command In SQL Sever

Truncate is used to delete all data in a table.It will delete the data page by page manner why because truncate is fast compare to delete.delete will maintain log and truncate will not maintain log.

Here my quation is If we use Transaction in our code Truncate can be rolled back or not .. If it is rollback what case it will rollback?

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

Update:Truncate is a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed:

We might have listened and read either of following sentence many many times.

“DELETE can be rolled back and TRUNCATE can not be rolled back”.
OR
“DELETE can be rolled back as well as TRUNCATE can be rolled back”.

While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE can not be rolled back using log files in full recovery mode.

DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.

In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files. If deallocated data files are overwritten by other data it can be recovered using rollback. There is no guarantee of the rollback in case of TRUNCATE. However, while using T-SQL following code demonstrates that TRUNCATE can be rolled back for that particular session.

BEGIN TRAN
TRUNCATE TABLE TestTable-- Following SELECT will return TestTable emptySELECT *FROM TestTable-- Following SELECT will return TestTable with original dataROLLBACK
SELECT * FROM TestTable

NOTE: DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

SQL Trigger Example For Update and Delete

USE [ONE_TAX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('[FIN_TAX].[OPL_OPINION_LOG_H_TRG]','TR') IS NOT NULL
DROP TRIGGER [FIN_TAX].[OPL_OPINION_LOG_H_TRG]
GO

CREATE TRIGGER [FIN_TAX].[OPL_OPINION_LOG_H_TRG]
ON [FIN_TAX].[OPL_OPINION_LOG]
AFTER UPDATE, DELETE
AS
BEGIN
INSERT INTO FIN_TAX.OPL_OPINION_LOG_H
(
OPL_SK,
DOCUMENT_ID,
TPL_PROJ_ID,
OPL_FINAL_VERSION_FLG_ID,
OPL_FIN48_FLG_ID,
OPL_INT_EXT_FLG_ID,
OPL_OPINION_DT,
OPL_FIRM_CONTACT_NAME,
OPL_AIG_CONTACT_ID,
OPL_ENTITY_REQUEST_ID,
OPL_ISSUE_DESC,
OPL_EXT_SHARE_FLG_ID,
OPL_COMPLETE_FLG_ID,
OPL_REVIEWER_NAME,
OPL_REVIEW_DT,
OPL_SUBMIT_DT,
OPL_UPLOAD_USERID,
OPL_FOREIGN_TEXT,
OPL_UPD_USERID,
OPL_UPD_TS,
OPL_ASSOC_TAX_PL_FLG_ID,
OPL_ISSUE_DISCUSS_FLG_ID,
OPL_DATE_VALID_FLG_ID,
OPL_SIGNIF_CHG_FLG_ID,
OPL_TAX_TREAT_TRANS_FLG_ID,
OPL_PRESENT_COMM_FLG_ID,
OPL_LITIGATE_FLG_ID,
OPL_FIRM_ISSED_OPINION_TEXT,
OPL_APPROVAL_STATUS_ID,
OPL_DOC_ACCESS_FLG_ID,
OPL_LEGAL_OPINION_FLG_ID,
OPL_AUTH_CONTRARY_FLG_ID,
HIST_ACTION_TYPE
)
SELECT
*,CASE WHEN EXISTS (SELECT * FROM inserted)THEN 'U' ELSE 'D' END
FROM deleted;
END;

GO