SQL Server/T-SQL Tutorial/Transact SQL/Try catch
Содержание
Another TRY/CATCH
<source lang="sql">
6> BEGIN TRY 7> SELECT 1/0 AS DivideByZero 8> SELECT 1 AS NoError 9> END TRY 10> BEGIN CATCH 11> SELECT "Exception Caught!" AS CatchMessage 12> END CATCH 13> GO DivideByZero
(0 rows affected) CatchMessage
Exception Caught! (1 rows affected) 1> 2></source>
Applying TRY...CATCH Error Handling Without Recoding a Stored Procedure
<source lang="sql">
5> CREATE PROCEDURE usp_SEL_DivideByZero 6> AS 7> SELECT 1/0 8> GO 1> 2> BEGIN TRY 3> EXEC dbo.usp_SEL_DivideByZero 4> END TRY 5> BEGIN CATCH 6> SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, 7> ERROR_LINE () ErrorLine, ERROR_MESSAGE() Msg 8> PRINT "This stored procedure did not execute properly." 9> END CATCH 10> GO
(0 rows affected) ErrorNBR Severity ErrorLine Msg
----------- ----------- ------------------------------------------------------------------------------------
8134 16 5 Divide by zero error encountered.
(1 rows affected) This stored procedure did not execute properly. 1></source>
Basic TRY/CATCH
<source lang="sql">
5> BEGIN TRY 6> SELECT 1/0 AS DivideByZero 7> END TRY 8> BEGIN CATCH 9> SELECT "Exception Caught!" AS CatchMessage 10> END CATCH 11> GO DivideByZero
(0 rows affected) CatchMessage
Exception Caught! (1 rows affected) 1></source>
Error Handling in SQL Server 2005
<source lang="sql">
4> 5> CREATE PROCEDURE spDeleteProduct @Productid int 6> AS 7> BEGIN TRY 8> BEGIN TRANSACTION 9> DELETE Product WHERE ProductID = @ID 10> COMMIT TRANSACTION 11> END TRY 12> BEGIN CATCH 13> DECLARE @Err AS int 14> DECLARE @Msg AS varchar(max) 15> SET @Err = @@Error 16> SET @Msg = Error_Message() 17> ROLLBACK TRANSACTION 18> INSERT ErrorTable 19> VALUES (@err, @msg) 20> END CATCH 21> GO 1> 2> 3> drop PROCEDURE spProduct_Delete; 4> GO
1> 2> 3> drop table Product; 4> GO</source>
Error Handling with TRY...CATCH
<source lang="sql">
3> 4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> 4> BEGIN TRY 5> BEGIN TRAN 6> INSERT employee(ID, first_Name)VALUES(1,"T") 7> INSERT employee(ID, first_Name)VALUES(11,"F") 8> COMMIT TRANSACTION 9> END TRY 10> BEGIN CATCH 11> SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, 12> ERROR_LINE () ErrorLine, ERROR_MESSAGE() Msg 13> ROLLBACK TRANSACTION 14> END CATCH 15> 16> 17> 18> drop table employee; 19> GO ErrorNBR Severity ErrorLine Msg
----------- ----------- ------------------------------------------------------------------------------------
2627 14 6 Violation of PRIMARY KEY constraint "PK__employee__41248F15". Cannot insert duplicat
e key in object "dbo.employee".
(1 rows affected)
1></source>
Nesting TRY...CATCH Calls
<source lang="sql">
4> 5> CREATE TABLE employee( 6> id INTEGER, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> CREATE PROCEDURE dbo.usp_employee @Name nvarchar(50),@GroupName nvarchar(50) 4> AS 5> BEGIN TRY 6> INSERT employee (first_Name, region)VALUES (@Name, @GroupName) 7> END TRY 8> BEGIN CATCH 9> BEGIN TRY 10> PRINT "The first department attempt failed." 11> INSERT employee(first_Name, region)VALUES ("Misc", @GroupName) 12> END TRY 13> BEGIN CATCH 14> PRINT "A Misc department for that group already exists." 15> END CATCH 16> END CATCH 17> GO 1> 2> EXEC dbo.usp_employee "E", "North" 3> GO (1 rows affected) 1> 2> EXEC dbo.usp_employee "G", "South" 3> GO (1 rows affected) 1> 2> drop table employee; 3> GO 1></source>
try...catch Demo
<source lang="sql">
7> CREATE TABLE SomeData 8> ( 9> SomeColumn INT 10> ) 11> GO 1> 2> BEGIN TRANSACTION 3> 4> BEGIN TRY 5> --Throw an exception on insert 6> INSERT SomeData VALUES (CONVERT(INT, "abc")) 7> END TRY 8> BEGIN CATCH 9> --Try to commit... 10> COMMIT TRANSACTION 11> END CATCH 12> GO Msg 3930, Level 16, State 1, Server J\SQLEXPRESS, Line 10 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. 1> 2> 3> drop table SomeData; 4> GO</source>
