Please read the following code
SET IMPLICIT_TRANSACTIONS ON BEGIN TRANSACTION DELETE FROM xxxx SELECT * FROM xxxxxxxx WHERE EXISTS ( SELECT * FROM xxxxx WHERE CONCAT(xxxxxxx) = CONCAT(xxxxxxx) ) COMMIT
After this I run the following SP
CREATE PROCEDURE [dbo].[xxxxxx] AS BEGIN ---To remove any duplicates WITH CTE AS( SELECT xxxxxx, xxxxxx, xxxx, RN = ROW_NUMBER()OVER(PARTITION BY xxxxx, xxxx ORDER BY xxxx) FROM xxxxxxx ) delete FROM CTE WHERE RN > 1 ---Insert and load insert into xxxxxxxx select xxxxxxx.* from xxxxxxx left join xxxxx on xxxxxxxxxxx=xxxxxxxx COLLATE SQL_Latin1_General_CP1_CI_AI where xxxxx is null truncate table xxxxx_Staging END
The above SP stuck and continue to run due to lock. Why my SP is stuck even I am using Being Transaction before Delete in the first staement. I dont want to use with(NOlock) option in the SP. Anyway to write SP in a better way.