Create Queue SQL Server 2012

What is @@ERROR in SQL? When we should use @@ERROR?

What is @@ERROR in SQL?
@@ERROR returns only current error information (error number and error) after T-SQL statements executed.

@@ERROR returns 0, if the previous SQL statement has no errors otherwise return 1.

@@ERROR is used in basic error handling in SQL Server and @@ERROR is a global variable of SQL and this @@ERROR variable automatically handle by SQL. If error is occurred set error number otherwise reset 0.

It is work only within the current scope and also contains the result for the last operation only.

Syntax: - @@ERROR 
Return Type: - INT

When we should use @@ERROR?
1.      While executing any stored procedures
2.      In the SQL statements like Select, Insert, Delete and Update etc.
3.      In the Open, Fetch Cursor.

When we should use Try Catch Block?
The Try Catch Block is generally used where want to catch errors for multiple SQL statements.

The example as,
-- DECLARE RETURN TABLE
DECLARE @Return_Table TABLE (Code varchar(10)
 ,Message varchar(100), ID varchar(100))

--UPDATE CUSTOMER QUERY
UPDATE Customer
 SET Name = N'Anil Singh'
WHERE Id = 0786

--USE OF @@ERROR
IF (@@ERROR <> 0)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'ERROR', 'An error occurred in updating the customer.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE IF (@@ERROR = 1087)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'ERROR', 'Must declare the table variable @Return_Table.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE IF (@@ERROR = 547)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'ERROR', 'A check constraint violation occurred.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'OK', 'SUCCESS', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END

GO

hope you are enjoying with this post! Please share with you friends!! Thank you!!!

ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^