------------------------------------------------------------------------- -- ERROR HANDLING /***************HANDALING ERRORS******************/ ---EXAMPLE OF TRY AND CATCH --Trying delete category number 1 DELETE FROM categories WHERE categoryid = 1 --Using TRY and CATCH to handle error BEGIN TRY DELETE FROM Categories WHERE CategoryID = 1 END TRY BEGIN CATCH SELECT 'Thats how you catch an error', error_number(), error_message() print error_message() END CATCH --EXAMPLE OF RAISEERROR --Updating product in order (order details table) - quantity greater than 0 SELECT * FROM [order details] WHERE orderid = 10250 BEGIN TRAN DECLARE @ord_id INT, @prod_id INT, @quan INT SET @ord_id = 10250 SET @prod_id = 41 SET @quan = 20 IF @quan > 0 UPDATE [order details] SET quantity = @quan WHERE productid = @prod_id AND orderid = @ord_id ELSE IF @quan <= 0 RAISERROR('Cannot update quantity to 0 or less', 16,1) select @@TRANCOUNT --view update SELECT * FROM [order details] WHERE orderid = 10250 --Working, now lets see how thats not working: BEGIN TRAN DECLARE @ord_id INT, @prod_id INT, @quan INT SET @ord_id = 10250 SET @prod_id = 41 SET @quan = -1 IF @quan > 0 UPDATE [order details] SET quantity = @quan WHERE productid = @prod_id AND orderid = @ord_id ELSE IF @quan <= 0 RAISERROR('Cannot update quantity to 0 or less', 16,1) ROLLBACK ------SP_ADDMESSAGE---- -------------------------------------------------------------------- -- Any case you want to add an error massage to the system: EXEC sp_addmessage 50001, 16, 'CAN NOT UPDATE QUANTITY TO 0 OR LESS' SELECT * FROM sys.messages --using the message - updating product in order to quantity 0 SELECT * FROM [order details] WHERE orderid = 10250 BEGIN TRAN DECLARE @ord_id INT, @prod_id INT, @quan INT SET @ord_id = 10250 SET @prod_id = 41 SET @quan = 0 IF @quan > 0 UPDATE [order details] SET quantity = @quan WHERE productid = @prod_id AND orderid = @ord_id ELSE IF @quan <= 0 RAISERROR (50001,16,1) ROLLBACK SELECT * FROM [order details] WHERE orderid = 10250 --DELETE MESSAGE ------------------------- EXEC sp_dropmessage 50001 SELECT * FROM sys.messages ---------------------------------------- --- Levels of Severity & Error State --- ---------------------------------------- -- When an error is raised by the SQL Server Database Engine, -- the severity of the error indicates the type of problem encountered by SQL Server. -- The following examples describes the severity levels of the errors raised by the SQL Server: -- Errors 11&16: Indicate errors that can be corrected by the user. -- Error 14: Indicates security-related errors, such as permission denied. -- Error 17: Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) -- Error state: Identifies the source from which the error was issued (error can be issued from more then one place) -----EXAMPLE OF RAISERROR WITHIN TRY - CATCH BLOCK ----------------------------------------------------- Use AdventureWorks2012 create table tst ( col1 int ) select * from tst begin try declare @v_val int = 1, @v_chk varchar(10) if @v_val = 1 begin insert into tst values(@v_val) set @v_chk = 'OK' end else begin set @v_chk = 'ERROR' end print @v_chk end try begin catch if @v_chk = 'OK' commit else rollback end catch select * from tst ------------------------ Alter Table Sales.SalesOrderHeader Drop Column LineTotal; -- Add New Colunm - LineTotal Alter Table Sales.SalesOrderHeader add LineTotal int check(LineTotal < 100) select * from Sales.SalesOrderHeader 43659 alter table Sales.SalesOrderHeader drop constraint CK__SalesOrde__LineT__72E607DB ALTER proc usp_UpdateSales (@OrderId int) as set nocount on begin try begin tran update Sales.SalesOrderHeader set LineTotal = TaxAmt * 10 where SalesOrderID = @OrderId commit end try begin catch if @@trancount > 0 begin select @@trancount rollback print 'Rollback' end declare @cmd nvarchar(255) set @cmd = 'error occurres in usp_UpdateSales: ' + ERROR_MESSAGE() raiserror (@cmd, 16, 1) set nocount off end catch EXEC dbo.usp_UpdateSales 43659 --Check data select * from Sales.SalesOrderHeader where SalesOrderID = 43659 -- Another Example: BEGIN TRY -- RAISERROR with severity 11-19 will cause exeuction to jump to the CATCH block. PRINT 1 RAISERROR ('Error raised in TRY block.', -- Message text. 16, -- Severity. 1 -- State. ); PRINT 1 END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE()+' Then catched. Then raised again...', @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH