Study Notes (5)

1. Transactions
Begin Tran start transaction, commit Tran commit transaction, rollback Tran roll back transaction, save Tran save transaction
The first three are very familiar. The last save Tran should be well understood and researched.
In fact, the name of the Save Tran save point is a bookmarkmark. For example, when many operations are performed in a transaction, a transaction is also divided into the first step, the second step, and the third step ..., if the first step is correct and the second step has an error, You need to roll back.
Add a save Tran firststep between them and roll back to the corresponding position through rollback Tran firststep.

2. Stored Procedure
When declaring the data type, you must also use the varying and output options when declaring the cursor type parameter.
Ooutput: output type parameter can be defined when output value is external
Return: all stored procedures actually return values. By default, a value of 0 is returned after the stored procedure is completed. to return a value, use return <integer return value>
If you want to return the stored procedure to prevent it from executing the following, you can directly use return.

3. handle errors
In SQL, you can also use try catch to handle errors. Of course, you can also use @ error to detect errors.

4. Manual generation Error
Raiserror (<message>, error level, status)
Example: raiserror ('error, please check the proc)

5. error level:
1-10: purely informative errors
11-16: If there is no error processing, the execution will be terminated and an error will be generated on the client. If there is an error processing block, the error processor will be called.
17: Only SQL server uses this level, which is very serious. For example, tampdb is full.
18-19: These two types of errors remind the system administrator
20-25: fatal error. User connection may be damaged
Note: The with log option is required for the second-class errors.

6. add your own error message
Add error message
Sp_addmessage [@ msgnum =] <Message ID> [@ severity =] <error level> [@ msgtext =] <'message string'> [, [@ lang =] <'linguistic '>] [, [@ with_log =] <true | false>] [, [@ replace =] 'repalce']
@ Replace: If you are editing an existing message instead of creating a new message, you must set this parameter to 'repalce'
Delete error messages
Sp_dropmessage