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,
I hope you are enjoying with this post! Please share with you friends!! Thank you!!!
-- 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 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..
|