@@ERROR

It Returns the error number for the last Transact-SQL statement executed.

When Microsoft® SQL Server™ completes the execution of a Transact-SQL statement,

• @@ERROR is set to 0 if the statement executed successfully.
• If an error occurs, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed.

You can view the text associated with an @@ERROR error number in the sysmessages system table.

As this variable is cleared and rest after each statement execution we are supposed to check it immediately following the statement being validated or just save it to a local variable that can be checked later.

 

Error Code	Description
0-10	It is Informational messages not actual error, actually 0 means No Error, No Information, before invoke the Programs, DB Engine converts to 0 then start performing operations
11-16	Error can be corrected by user, this may be syntax error
11	Object Doesn't Exists
12	Don't allow to do lock on Any Object
13	Transaction Dead Lock Errors
14	Security related Error, access denied
15	Syntax Error
16	General Error like invalid arguments, string value not quoted properly etc.,
17-19	Software Error, not corrected by User
17	Out of memory exception, disk usage, lock, write protected, no access to resource etc.,
18	DB Engine related error
19	Non-Configurable limit exceeded with DB Engine
19-25	Note: 19-25 error will be updated in SQL Error Log
20-25	Fatal Error occurred based on single or batch process running currently
20	Problem with current Task only
21	problem affects all other process
22	Table or Index Damaged by software or hardware. It occurs rarely. Run DBCC CHECKDB to determine error
23	Problem with integrity of Database, corrupted
24	Need to restore database, database may be corrupted, may be hardware issue
25	System Error