server|sqlserver| Stored Procedures | error handling in SQL Server stored processes simply returns an error to the client, and typically the access provider can
Do it yourself. But at least you know what's wrong with T-SQL when it comes to execution. In this article, I will introduce the
You're wrong
SQL Server has nearly 3,800 predefined error codes that are maintained by the sysmessages table in the home directory. Every single mistake.
The error code has a corresponding level of precision, which describes the errors in general. The level of the error definition is from 0 to 25. More than 20 of
Errors represent significant errors, which usually mean that the error causes the storage process to terminate immediately and that all client connections are heavy
New initialization. Non-critical errors simply disable the currently running program line and continue execution. All the error codes have a pre
So what do you do when you get this information? An error occurred while accessing the stored process, and you can pass the @ @ERROR
The system function returns an error code. You can get real error messages and levels by looking at sysmessages.
Error Handling Basics
Suppose there is a table called nonullsallowed, with two fields Field1 and Field2. Can not be empty. Below the
CREATE PROCEDURE Illegalinsert as
INSERT nonullsallowed VALUES (null, NULL)
Print ' Error occurred '
Print @ @ERROR
Suppose you have the following process:
CREATE PROCEDURE Sampleprocedure
As
EXEC Illegalinsert
Print @ @ERROR
Errors occur when Sampleprocedure is invoked through Query Analyzer.
Server:msg 515, level, state 2, Procedure Illegalinsert, line 2
Cannot insert the value NULL into column ' Field2 ', table
' Tempdb.dbo.NoNullsAllowed '; Column does not allow S. INSERT fails.
The statement has been terminated.
Error occurred
0
0
This little experiment can tell you something.
@ @ERROR function simply returns an error code
If the @ @ERROR returns 0, there is no error.
Statements that cause non critical errors are skipped and the process continues to execute the next statement.
Basic strategy
From these three points, you can design a strategy. In reality, the actual situation is almost impossible to formulate a strategy, but from a certain point of view
Checking each row in a stored process is impractical, so you should check for key areas such as key statements
If the following statement relies on the preceding statement, a manual termination process is required after the error is detected.
Relies on the access provider to notify the customer of errors.
Generate an error
RaiseError can produce custom error messages or numbers to return to the client. Here is the syntax for Rasieerror,
Table A is an explanation of each parameter.
RAISERROR ({msg_id|msg_str}{, severity, state}
[argument [,... N]])
[With option [,... N]]
The raiseerror can be used first to produce predefined error codes. RaiseError receives more than 13000 error codes
In fact, RAISERROR is more used to send custom error messages. As shown in Figure A, the msg_str identifier can be wrapped
Any error message, which means that RAISERROR can return a specific error message to the customer.
Custom error messages
Of course, in most cases, the error message is used multiple times, and it is clear that the same message is entered again and again. Make
Use the sp_addmessage stored procedure to add custom error messages to the sysmessages table. Later, in the use of
The identity number of the custom error message must be greater than or equal to 50000, and the information itself cannot be longer than 255 characters. Below
is an example of creating new information, giving the identification number 55555, the level of 10,sp_addmessage
The storage process needs to be called like this.
Sp_addmessage 55555, the ' New error message. '
You can add new errors to the storage process:
RAISERROR 55555, 10
Not hard, just different.
It is not difficult to create a stored process with error detection, just as it is most likely to be used. Only need a
A good plan and relevant knowledge can be easily used by him.
