September 3, 2009 at 3:52 pm
Hello.
I have the following procedure that is not going to the ERR_HANDLER: upon update error. I am passing an invalid userID so code should go ERR_HANDLER:.
ALTER PROCEDURE dbo.updateLastName
(
@userid varchar(7),
@lastname varchar(15)
)
AS
DECLARE @err INT
BEGIN
BEGIN TRANSACTION
UPDATE [USER_TBL] SET [lastname] = @lastname
WHERE [userID] = @userid;
SELECT @err = @@error
IF @err <> 0
GOTO ERR_HANDLER
ELSE
COMMIT TRANSACTION
END
ERR_HANDLER:
BEGIN ROLLBACK TRANSACTION
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END
server gives me this error message when debugging. Thank you for your time.
(0 row(s) affected)
Msg 3903, Level 16, State 1, Procedure updateLastName, Line 19
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 28102, Level 16, State 1, Procedure updateLastName, Line 21
Batch execution is terminated because of debugger request.
September 3, 2009 at 4:04 pm
It won't go to the error handler if you pass a userID that affects no rows. Updating 0 rows is not an error. Something like a constraint violation, data conversion problem would be an error.
Also, you need to prevent the successful code from reaching the ERR_HANDLER: label, or it will try to rollback a transaction that's already committed. That's why you're getting the error you are.
Since you're using SQL 2005, consider TRY ... CATCH rather than using @@Error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2009 at 4:22 pm
Thank you very much Gail. I have updated as follows but like you said the UPDATE is not generating an error since no rows were affected? Is there a way to evaluate the number of rows from the update and then explicitly go to the catch block? A little more background is that I am wrapping this store procedure in a C# DataSet Table Adapter. I want to catch any errors in the C# method. The wrapper method doesn't throw and error either. Maybe I need to test to see if the user exists first?
Thank you very much!
ALTER PROCEDURE [dbo].[updateLastName]
(
@userid varchar(7),
@lastname varchar(15)
)
AS
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.USER_TBL SET lastname = @lastname WHERE userID = @userid
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
September 3, 2009 at 4:34 pm
jmiaebrown (9/3/2009)
Is there a way to evaluate the number of rows from the update and then explicitly go to the catch block?
It's easy to evaluate the number of rows affected, but do you really want to go to the catch block? Updating 0 rows is not an error, there won't be an ERROR_MESSAGE() or ERROR_SEVERITY(). This is something that you need to handle yourself if you consider it a problem.
Check @@RowCount for the number of rows affected by the update. It has to be checked right after the update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2009 at 8:37 pm
Thank you! This seemed to work for me.
ALTER PROCEDURE [dbo].[updateLastName]
(
@userid varchar(7),
@lastname varchar(15)
)
AS
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.USER_TBL SET lastname = @lastname WHERE userID = @userid
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ERROR UPDATING USER_TBL RECORD', 16, 1)
RETURN
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
September 4, 2009 at 12:42 am
Three comments.
1. Get in the habit of first storing @@ROWCOUNT in an integer variable. ( I use @rc. ) Do this to preserve the value in case we want to return it or use it again. @@ROWCOUNT is reset to 0 immediately after you test it.
2. Rather than the generic "Error in updating" message, why not make you text more explicit and say "No target rows were found to update." In the future, being specific may help you or others diagnose and correct a problem much quicker.
3. If all you are doing in the transaction is the update... and if no rows are updated... why do you feel the need to do a rollback? The target table is unchanged.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 4, 2009 at 7:57 am
Bob Hovious (9/4/2009)
Three comments.1. Get in the habit of first storing @@ROWCOUNT in an integer variable. ( I use @rc. ) Do this to preserve the value in case we want to return it or use it again. @@ROWCOUNT is reset to 0 immediately after you test it.
2. Rather than the generic "Error in updating" message, why not make you text more explicit and say "No target rows were found to update." In the future, being specific may help you or others diagnose and correct a problem much quicker.
3. If all you are doing in the transaction is the update... and if no rows are updated... why do you feel the need to do a rollback? The target table is unchanged.
Very helpful. Thank you for your comment. I have implemented your suggestions for 1 and 2. For 3, I was getting a TRANCOUNT error until I added the ROLLBACK TRANSACTION. Something about the number of commits being inconsistent. I have updated as follows.
ALTER PROCEDURE [dbo].[updateLastName]
(
@userid varchar(7),
@lastname varchar(15)
)
AS
DECLARE @rc int
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.USER_TBL SET lastname = @lastname WHERE userID = @userid
SELECT @rc = @@ROWCOUNT
IF @rc = 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('NO TARGET ROWS ON USER_TBL FOUND TO UPDATE', 16, 1)
RETURN
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
September 4, 2009 at 8:27 am
I think part of the question is why an explicit transaction for a single update?
You could remove both the ROLLBACK and the RETURN and not change the behaviour, nothing'll be committed if the only update in the transaction updated 0 rows
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2009 at 8:34 am
GilaMonster (9/4/2009)
I think part of the question is why an explicit transaction for a single update?You could remove both the ROLLBACK and the RETURN and not change the behaviour, nothing'll be committed if the only update in the transaction updated 0 rows
Thank you. Gotcha. I removed the ROLLBACK and RETURN. I will do more studying on TRANSACTIONS. I am sure I will encounter more issues as I continue development.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply