This article demo TRY/CATCH, THROW(),RAISERROR() and ERROR functions.
THROW
CREATE DATABASE TSQLDB;
USE TSQLDB;
create table dbo.testThrow (
ID int PRIMARY KEY
);
-- Use CATCH
BEGIN TRY
INSERT dbo.testThrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.testThrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'Catch Block.';
END CATCH;
-- Throw
BEGIN TRY
INSERT dbo.testThrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.testThrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'Catch Block.';
THROW;
END CATCH;
The first statement will catch the error but it wont display the actually error message because it has been catch.
After we add the THROW, it will also display the actual error message
Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK__testThro__3214EC2789EB1AE6'. Cannot insert duplicate key in object 'dbo.testThrow'. The duplicate key value is (1).
THROW Custom error
We can also use throw to throw the custom error. If we use the throw the custom error, the severity level would always be 16.
BEGIN TRY
INSERT dbo.testThrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.testThrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'Catch Block.';
-- THROW;
THROW 50000,'I throw you TEST',1;
END CATCH;
RAISEERROR
RAISEERROR is used to raise the custom error message or use sp_addmessage into the sys,messages so it can be reused again.
EXEC sp_addmessage @msgnum = 50005,
@severity = 10,
@msgtext = N'This is from sys.message';
GO
BEGIN TRY
INSERT dbo.testRAISEERROR(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.testRAISEERROR(ID) VALUES(1);
END TRY
BEGIN CATCH
RAISERROR (N'This is message Violation of PRIMARY KEY constraint.', -- Message text.
17,-- Severity.
1 -- State
);
RAISERROR (50005, -- Message id.
14, -- Severity,
1 -- State
);
END CATCH;
EXEC sp_dropmessage 50005;
ERROR FUNCTONS
BEGIN TRY
INSERT dbo.testThrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.testThrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'Catch Block.' ;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
0 comments:
Post a Comment