January 20th, 2004, 11:57 PM
is it ok to call raise errors in this way
I am jsut wondering whether it is considered acceptable that after an error catch to contunue on without nesting it in a if / else. Here is an example
if @@error <> 0
raiserror('some error messsage',16,1)
//continue with normal code
I know that the level 16 (or however it is referred to) terminates the procedure, but I am wondering if someone would say that the error level could change and therefore you should contain the remaining code in a begin end block
January 21st, 2004, 12:08 AM
It depends how serious the error is. If you are writing a tight multi-transactional sproc I would definitly use error checking and rollbacks.
If it is a couple simple lines where the resulting would not affect the state of the database I would not worry about.
I am not 100% certain what errors do and do not exit procedures when fired. Anyone??
January 21st, 2004, 01:01 AM
sorry I maybe wasn't very clear. Normally I would include transactions but left them out to keep thing simple.
What I meant was to assume that if the error is called that the procedure will quit and therefore all the remaining commands do not have to be within an else block because they will never run if there is an error.
Hope that was more clear
January 21st, 2004, 01:23 AM
If I understand it properly, Victor please help me here, except for critical errors encounter by SQL Server, ie. deadlocks or lost comunication, the error is simply returned to the app (sproc, trigger, etc) and it is up to the user handle the error and manually rollback the transaction. There is also an autocommit mode which automatically rollback or commit a transaction depending on if there is an error or not. Be careful with this mode as commits are execute after every tsql statement, transaction blocks are ignored.
Here is the excerpt from BOL
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.
Rolling Back an Individual Transaction
If any errors occur during a transaction, the instance of SQL Server uses the information in the log file to roll back the transaction. This rollback does not affect the work of any other users working in the database at the same time. Usually, the error is returned to the application, and if the error indicates a possible problem with the transaction, the application issues a ROLLBACK statement. Some errors, such as a 1205 deadlock error, roll back a transaction automatically. If anything stops the communication between the client and an instance of SQL Server while a transaction is active, the instance rolls back the transaction automatically when notified of the stoppage by the network or operating system. This could happen if the client application terminates, if the client computer is shut down or restarted, or if the client network connection is broken. In all of these error conditions, any outstanding transaction is rolled back to protect the integrity of the database.
Last edited by abombss; January 21st, 2004 at 01:26 AM.