TY, quick question suppose if i am using two If exists blocks here can i keep it in same try catch block ?
Alter procedure CATOI
@col1 nvarchar(10)
AS
BEGIN TRANSACTION
IF EXISTS(SELECT 1 FROM dbo.Std WHERE col1= @col1)
BEGIN
DELETE FROM dbo.Std WHERE col1= @col1
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
INSERT INTO dbo.dbo.Std
(
col1
)
SELECT
PN
FROM stage.Std
WHERE col1 = @col1
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
IF EXISTS(SELECT 1 FROM dbo.HR WHERE col1= @col1)
BEGIN
DELETE FROM dbo.HR WHERE col1= @col1
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
INSERT INTO dbo.HR
(
col1
)
SELECT
PN
FROM stage.HR
WHERE col1 = @col1
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
FINISH:
COMMIT TRANSACTION
RETURN 0 ERROR:
ROLLBACK TRANSACTION
RETURN -1
END
You can have as many TRY/CATCH blocks as you need. You could easily add another using the same technique as my original query uses, but the question is, how do you plan to handle it if more than one of your attempts fails? Does it matter? If not, then just include the additional IF block and give it's own transaction, and let the single CATCH block handle errors from that portion the same way it handles the others.