в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:23:51Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/System Settings/ERROR - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия