0

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.

4

You turned on IMPLICIT_TRANSACTION mode, which will start a new transaction on any statement that writes to the database. And that transaction must be committed or rolled back explicitly, even if the transaction is started inside a stored procedure.

EG

use tempdb
go
drop table if exists t
create table t(id int)
go

create or alter procedure foo
as
begin
  insert into t(id) values (1)
end
go
SET IMPLICIT_TRANSACTIONS ON
exec foo

select @@trancount

outputs

1
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.