SQL Server/T-SQL Tutorial/System Settings/ERROR

A stored procedure that uses the @@ERROR system function

   <source lang="sql">

7> create table Billings ( 8> BankerID INTEGER, 9> BillingNumber INTEGER, 10> BillingDate datetime, 11> BillingTotal INTEGER, 12> TermsID INTEGER, 13> BillingDueDate datetime , 14> PaymentTotal INTEGER, 15> CreditTotal INTEGER 16> 17> ); 18> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> CREATE PROC spInsertBilling 4> @BankerID int, @BillingNumber varchar(50), 5> @BillingDate smalldatetime, @BillingTotal money, 6> @TermsID int, @BillingDueDate smalldatetime 7> AS 8> DECLARE @ErrorVar int 9> INSERT Billings (BankerID) 10> VALUES (@BankerID) 11> SET @ErrorVar = @@ERROR 12> IF @ErrorVar <> 0 13> BEGIN 14> IF @ErrorVar = 547 15> PRINT "Not a valid BankerID!" 16> ELSE 17> PRINT "An unknown error occurred." 18> RETURN @ErrorVar 19> END 20> GO 1> 2> 3> drop PROC spInsertBilling; 4> GO 1> 2> 3> drop table Billings; 4> GO</source>

Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR

   <source lang="sql">

4> CREATE TABLE T1( 5> pk_col int NOT NULL PRIMARY KEY CHECK (pk_col > 0), 6> ident_col int NOT NULL IDENTITY (1,1) 7> ) 8> 9> DECLARE 10> @myerror AS int 11> INSERT INTO T1 VALUES(0) -- violate the check constraint 12> SET @myerror = @@ERROR 13> IF @myerror = 2627 14> PRINT "PRIMARY KEY constraint violation" 15> ELSE IF @myerror = 547 16> PRINT "CHECK constraint violation" 17> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 11 The INSERT statement conflicted with the CHECK constraint "CK__T1__pk_col__3C54ED00". The conflict occurred in database "master", table "dbo.T1", column "pk_col". The statement has been terminated. CHECK constraint violation 1> 2> --Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR 3> DECLARE 4> @myerror AS int, 5> @myrowcount AS int, 6> @myidentity AS int 7> INSERT INTO T1 VALUES(10) -- PK violation 8> SELECT @myidentity = @@IDENTITY, 9> @myrowcount = @@ROWCOUNT, 10> @myerror = @@ERROR 11> PRINT "@myidentity: " + CAST(@myidentity AS varchar) 12> PRINT "@myrowcount: " + CAST(@myrowcount AS varchar) 13> PRINT "@myerror  : " + CAST(@myerror AS varchar) 14> GO (1 rows affected) (1 rows affected) @myidentity: 2 (1 rows affected) @myrowcount: 1 (1 rows affected) @myerror  : 0 1> --Output of Successful Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR 2> 3> drop table T1; 4> GO</source>

Check the @@ERROR with if statement

   <source lang="sql">

11> create table Billings ( 12> BankerID INTEGER, 13> BillingNumber INTEGER, 14> BillingDate datetime, 15> BillingTotal INTEGER, 16> TermsID INTEGER, 17> BillingDueDate datetime , 18> PaymentTotal INTEGER, 19> CreditTotal INTEGER 20> 21> ); 22> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> CREATE PROC spInsertBilling 4> @BankerID int, @BillingNumber varchar(50), 5> @BillingDate smalldatetime, @BillingTotal money, 6> @TermsID int, @BillingDueDate smalldatetime 7> AS 8> DECLARE @ErrorVar int 9> INSERT Billings (BankerID) 10> VALUES (@BankerID) 11> SET @ErrorVar = @@ERROR 12> IF @ErrorVar <> 0 13> BEGIN 14> IF @ErrorVar = 547 15> PRINT "Not a valid BankerID!" 16> ELSE 17> PRINT "An unknown error occurred." 18> RETURN @ErrorVar 19> END 20> GO 1> 2> DECLARE @ReturnVar int 3> EXEC @ReturnVar = spInsertBilling 4> 799,"ZXK-799","2002-07-01",299.95,1,"2001-08-01" 5> PRINT "Return code was: " + CONVERT(varchar,@ReturnVar) 6> GO (1 rows affected) (1 rows affected) Return code was: 0 1> 2> 3> drop PROC spInsertBilling; 4> GO 1> 2> 3> drop table Billings; 4> GO 1></source>

connection-level exception

   <source lang="sql">

6> CREATE PROCEDURE ConversionException 7> AS 8> BEGIN 9> SELECT CONVERT(INT, "abc") 10> END 11> GO 1> 2> 3> EXEC ConversionException 4> PRINT "This will NOT print!" 5> GO Msg 245, Level 16, State 1, Server J\SQLEXPRESS, Procedure ConversionException, Line 9 Conversion failed when converting the varchar value "abc" to data type int. 1> 2> drop PROCEDURE ConversionException; 3> GO</source>

@@error is

   <source lang="sql">

After each Transact-SQL statement, the server sets the value of this variable to an integer value: 0 If the statement was successful Error number If the statement has failed</source>

@@ERROR reset

   <source lang="sql">

6> SELECT 1/0 AS DivideByZero 7> IF @@ERROR <> 0 8> SELECT @@ERROR AS ErrorNumber 9> GO Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 6 Divide by zero error encountered.</source>

Observing line numbers in exceptions

   <source lang="sql">

6> SELECT 1 7> GO

---

         1

(1 rows affected) 1> SELECT 2 2> GO

---

         2

(1 rows affected) 1> SELECT 1/0 2> GO Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 1 Divide by zero error encountered. 1> 2></source>

Parse exception

   <source lang="sql">

3> SELECTxzy FROM SomeTable 4> PRINT "This will NOT print!" 5> GO Msg 156, Level 15, State 1, Server J\SQLEXPRESS, Line 3 Incorrect syntax near the keyword "FROM". 1></source>

== SELECT/td>

   <source lang="sql">

3> SELECT 5 / 0 4> 5> SELECT @@ERROR 6> GO Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 3 Divide by zero error encountered.

---

      8134

(1 rows affected)</source>

SELECT * FROM master.dbo.sysmessages WHERE error = @@ERROR

   <source lang="sql">

4> SELECT 5 / 0 5> SELECT * FROM master.dbo.sysmessages WHERE error = @@ERROR 6> GO Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 4 Divide by zero error encountered. error severity dlevel description

                                                                                   msglangid

---

-------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---

---------

      8134       16      0 Divide by zero error encountered.
                                                                                        1033

(1 rows affected)</source>

Using @@ERROR

   <source lang="sql">

7> SELECT 1/0 AS DivideByZero 8> SELECT @@ERROR AS ErrorNumber 9> GO Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 7 Divide by zero error encountered.</source>