python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
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

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like
www.code-sample.com/. Powered by Blogger.
ASK Questions