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

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

ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book