March 27, 2017 at 10:53 am
I have a procedure where it has old type of error handling how can i change it to 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
GPN
FROM stage.Std
WHERE col1 = @col1
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
FINISH:
COMMIT TRANSACTION
RETURN 0
ERROR:
ROLLBACK TRANSACTION
RETURN -1
March 27, 2017 at 11:06 am
mcfarlandparkway - Monday, March 27, 2017 10:53 AMI have a procedure where it has old type of error handling how can i change it to try catch block?
Alter procedure CATOI
@col1 nvarchar(10)
AS
BEGIN TRANSACTIONIF 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
GPN
FROM stage.Std
WHERE col1 = @col1IF @@ERROR <> 0
BEGIN
GOTO ERROR
ENDFINISH:
COMMIT TRANSACTION
RETURN 0ERROR:
ROLLBACK TRANSACTION
RETURN -1
This is how I would do it:ALTER PROCEDURE CATOI (
@col1 nvarchar(10)
)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM dbo.Std WHERE col1= @col1)
BEGIN
BEGIN TRY
BEGIN TRAN T1;
DELETE S
FROM dbo.Std AS S
WHERE S.col1= @col1;
INSERT INTO dbo.dbo.Std (col1)
SELECT GPN
FROM stage.Std
WHERE col1 = @col1;
COMMIT TRAN T1;
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK TRAN T1;
RETURN -1;
END CATCH
END;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 27, 2017 at 11:42 am
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
March 27, 2017 at 12:17 pm
mcfarlandparkway - Monday, March 27, 2017 11:42 AMTY, 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 TRANSACTIONIF 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 = @col1IF @@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 = @col1IF @@ERROR <> 0
BEGIN
GOTO ERROR
ENDFINISH:
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 27, 2017 at 1:13 pm
no that it not situation.@col1 parameter is unique, If the record already exists it will delete and again inserted.
This proc is calling form another proc where it has cursor in it.if on record fails it will pass to this Sp and rest will process.
March 27, 2017 at 1:24 pm
The logic which i did is correct?
ALTER PROCEDURE [dbo].[ICATOU]
@ID nvarchar(40)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM dbo.Emp WHERE ID=@ID)
BEGIN
BEGIN TRY
BEGIN TRANSACTION T1
DELETE EM
FROM dbo.Emp EM
WHERE EM.ID= @ID
INSERT INTO dbo.Emp
(
ID )
SELECT
ID
FROM stage.Emp
WHERE ID = @ID
COMMIT TRANSACTION T1
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
RETURN -1;
END CATCH
END
IF EXISTS(SELECT 1 FROM dbo.Std WHERE ID = @ID)
BEGIN
BEGIN TRY
BEGIN TRANSACTION T2
DELETE St
FROM dbo.Std St
WHERE St.ID = @ID
INSERT INTO dbo.Std
(
ID
)
SELECT
ID
FROM stage.Std
where ID = @ID
COMMIT TRANSACTION T2
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T2
RETURN -1;
END CATCH
END
END
March 28, 2017 at 6:52 am
mcfarlandparkway - Monday, March 27, 2017 1:24 PMThe logic which i did is correct?
ALTER PROCEDURE [dbo].[ICATOU]
@ID nvarchar(40)AS
BEGIN
SET NOCOUNT ONIF EXISTS(SELECT 1 FROM dbo.Emp WHERE ID=@ID)
BEGIN
BEGIN TRY
BEGIN TRANSACTION T1DELETE EM
FROM dbo.Emp EM
WHERE EM.ID= @ID
INSERT INTO dbo.Emp
(
ID )
SELECT
ID
FROM stage.Emp
WHERE ID = @IDCOMMIT TRANSACTION T1
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
RETURN -1;
END CATCH
ENDIF EXISTS(SELECT 1 FROM dbo.Std WHERE ID = @ID)
BEGIN
BEGIN TRY
BEGIN TRANSACTION T2
DELETE St
FROM dbo.Std St
WHERE St.ID = @IDINSERT INTO dbo.Std
(
ID
)
SELECT
ID
FROM stage.Std
where ID = @IDCOMMIT TRANSACTION T2
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T2
RETURN -1;
END CATCH
END
END
With that logic, you're never going to get to your second part of the procedure, because you allow the RETURN 0 to happen before you get there. That's why I asked about what you expect to occur if there are multiple failures. That inherently asks what to do when you have multiple things you want to accomplish, but perhaps need to trap errors separately? The point here is that if you use a RETURN statement within a TRY block, and it succeeds, it will never get to ANY of the other code. If you want multiple TRY/CATCH blocks, then you can't use a RETURN statement in the TRY portions until the last TRY block. You have to decide exactly what you want to do if an error occurs after you've already succeeded in another transaction, which once committed, won't be able to be rolled back. Otherwise, you'll have to do ALL the work inside a single transaction, and roll the whole thing back if any part fails, and that would mean just one TRY block and just one CATCH block, and just one transaction.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 7:49 am
I understand, With this code that you mention I am not able to get any record into insertion part
ALTER PROCEDURE CATOI (
@col1 nvarchar(10)
)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM dbo.Std WHERE col1= @col1)
BEGIN
BEGIN TRY
BEGIN TRAN T1;
DELETE S
FROM dbo.Std AS S
WHERE S.col1= @col1;
INSERT INTO dbo.dbo.Std (col1)
SELECT GPN
FROM stage.Std
WHERE col1 = @col1;
COMMIT TRAN T1;
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK TRAN T1;
RETURN -1;
END CATCH
END;
GO
March 28, 2017 at 9:15 am
Okay, so have you used a parameter that actually matches a value in the table being selected from?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 10:05 am
Yeah,Yes.
It will check for If exists if there is no record it will not go inside right?
I changed the code like below - The issue is after changing I am able to see the records into dbo..Std table, But CATOI procedure is calling from another procedure where I am capturing rowcount; How many rows are entered into std table. That count is showing as 0 for me. but I see the records in the table. Not sure why the Rowcount is showing 0
------------------------------------------------------------------------------------
EXEC dbo.CATOI @ID
SET @RowCount = @@ROWCOUNT;
SET @ErrorRows = @ErrorRows + @RowCount;
------------------------------------------------------------------------------------------------------------
Alter procedure CATOI
@ID nvarchar(10)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION
BEGIN TRY
IF EXISTS(SELECT 1 FROM dbo.Std WHERE ID = @ID)
BEGIN
DELETE FROM dbo.Std WHERE ID = @ID
END
INSERT INTO dbo..Std
(
ID
)
SELECT
ID
FROM stage.Std
WHERE ID = @ID
FINISH:
COMMIT TRANSACTION T1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
Exec LogError 'CATOI', @ID
END CATCH
END
March 28, 2017 at 11:29 am
That's because every SELECT or INSERT or UPDATE affects @@ROWCOUNT. It's not a reliable tool when you have triggers involved.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply