Error Handling

Error functions

• ERROR_NUMBER() : returns the number of the error
• ERROR_SEVERITY() : returns the error severity (11-19)
• ERROR_STATE() : returns the state of the error
• ERROR_LINE() : returns the number of the line of the error
• ERROR_PROCEDURE() : returns the name of stored procedure/trigger. NULL if there is not stored procedure/trigger
• ERROR_MESSAGE(): returns the error messages

RAISERROR syntax

IF NOT EXISTS ( Query ) 
	RAISERROR('message', ~ );

THROW syntax

recommended by Microsoft over RAISERROR

• THROW vs. RAISERROR

  ref: https://parksuseong.blogspot.com/2018/10/mssql-raiseerror-vs-throw.html

more details ) ref: https://sqlhints.com/tag/raiserror-vs-throw/

• THROW는 오류 메시지 없이 사용이 가능하고, RAISERROR는 오류 코드를 등록한 다음 오류 메시지를 정의해 주어야 한다?

THROW에서 오류 메시지를 정의하는 방법

SELECT * FROM sys.messages

Transactions

What is Transactions

Transfer $100 from account A to B

1. Subtract $100 from acc A
2. Add $100 to acc B

Operation 2 FAILS -> Can't subtract $100 from acc A

• START TRANSACTION
• COMMIT
• ROLLBACK

TRANCOUNT and savepoints

• Number of BEGIN TRAN statements that are active in the current connection.

• RETURNS greater than 0 : open transaction

• RETURNS 0 : no open transaction

• savepoints:

SAVE TRAN savepoint 1;

XACT_ABORT

• XACT_ABORT : specifies whether the current trasaction will be automatically rolled back when an error occurs.

Transaction isolation levels

• Concurrency: two or more transactions that read/change shared data at the same time

• isolate our transaction from other transactions

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

: read rows modified by other transactions without been committed/rolled back

• READ COMMITTED : faster, don't disturb other transactions <-> Allows dirty reads

SNAPSHOT