====================================================================================================================================================================================

EXCEPTION HANDLING----

=>EXCEPTIONS ARE THE RUNTIME ERRORS
=> WE CAN HANDLE THESE EXCEPTIONS OR RUNTIME ERRORS IN SQL SERVER
=> TO HANDLE THESE RUN TIME ERRORS WE USE TRY CATCH BLOCK

BEGIN TRY
STATEMENT1 ---THOSE STATEMENTS WHICH MAY RAISE EXCEPTION OR CAUSE RUNTIME ERRORS
STATEMENT2
STATEMENT3
END TRY
BEGIN CATCH
   STATEMENTS ---WHICH HANDLES EXCEPTION --STATEMENTS WHICH YOU WANT TO EXECUTE IF EXCEPTION
           ---   IS RAISED
END CATCH

=> IN TRY BLOCK IF STATEMENTS CAUSES RUNTIME ERROR THEN CONTROL IS TRANSFERED TO CATCH
BLOCK AND EXECUTES THE STATEMENTS IN CATCH BLOCK

SELECT 100/0


DECLARE @A INT, @B INT, @C INT
SET @A = 100
SET @B = 0
SET @C = @A/@B
PRINT @C

-------------RUNTIME ERROR , WE CAN HANDLE SUCH ERRORS THROUGH EXCCEPTION HANDLING
Msg 8134, Level 16, State 1, Line 29
Divide by zero error encountered.
 
TRY BLOCK
ERROR
I THINK UR DENOMINATOR IS ZERO


DECLARE @A INT, @B INT, @C INT
SET @A = 100
SET @B = 0
BEGIN TRY
PRINT 'TRY BLOCK'
SET @C = @A/@B
PRINT @C
END TRY
BEGIN CATCH
PRINT 'ERROR'
PRINT 'I THINK UR DENOMINATOR IS ZERO'
END CATCH


=======================================
DECLARE @A INT, @B INT, @C INT
SET @A = 100
SET @B = 10
BEGIN TRY
PRINT 'TRY BLOCK'
SET @C = @A/@B
PRINT @C
END TRY
BEGIN CATCH
PRINT 'ERROR'
PRINT 'I THINK UR DENOMINATOR IS ZERO'
END CATCH

OUTPUT:
TRY BLOCK
10
=====================================================
NOTE --->  
TO SEE THE ERROR NUMBERS

SELECT * FROM SYSMESSAGES 

WHERE ERROR = 547


8134	16	0	Divide by zero error encountered.	1033

PRINT CUSTOM ERROR MESSAGE AS PER OUR EXCEPTION
=============================================

DECLARE @A INT, @B INT, @C INT
SET @A = 100
SET @B = 0
BEGIN TRY
SET @C = @A/@B
PRINT @C
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134
PRINT 'DIVISOR CAN NOT BE ZERO'
END CATCH


OUTPUT:
DIVISOR CAN NOT BE ZERO

=========================================================
CREATE TABLE EMP250
(EMPNO INT PRIMARY KEY,
ENAME VARCHAR(10) NOT NULL,
SAL MONEY CHECK(SAL >= 3000)
);

--TRUNCATE TABLE EMP250

SELECT * FROM EMP250

DECLARE @ENO INT, @NAME VARCHAR(10), @SAL MONEY
BEGIN TRY
SET @ENO = 102
SET @NAME = 'POOJA'
SET @SAL = 10000
INSERT INTO EMP250 VALUES(@ENO,@NAME,@SAL)
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
IF ERROR_NUMBER() = 2627
PRINT 'ENO SHOULD NOT BE DUPLICATE'
ELSE IF ERROR_NUMBER() = 515
PRINT 'NAME CAN NOT BE NULL'
ELSE IF ERROR_NUMBER() = 547
PRINT 'SAL SHOULD BE MORE THAN 3000'
END CATCH


============================================================
DECLARE @A INT, @B INT, @C INT
SET @A = 100
SET @B = 0
BEGIN TRY
SET @C = @A/@B
PRINT @C
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT 'I THINK UR DENOMINATOR IS ZERO'
END CATCH

Divide by zero error encountered.
I THINK UR DENOMINATOR IS ZERO



======================================================================================

DECLARE @A INT, @B INT, @C INT
SET @A = 100
SET @B = 0
BEGIN TRY
SET @C = @A/@B
PRINT @C
END TRY
BEGIN CATCH
PRINT @@ERROR
PRINT ERROR_NUMBER()
PRINT ERROR_LINE()
PRINT ERROR_MESSAGE()
PRINT 'I THINK UR DENOMINATOR IS ZERO'
END CATCH

OUTPUT:
======
8134
8134
6
Divide by zero error encountered.
I THINK UR DENOMINATOR IS ZERO


USER DEFINED ERRORS/EXCEPTIONS:
==============================
EXCEPTION RAISED BY USER ARE CALLED USER DEFINED EXCEPTION OR ERRORS
USER DEFINED EXCEPTIONS CAN BE RAISED BY USER BY USING
RAISERROR(ERROR MSG, SEVERITY LEVEL, STATE)

SEVERITY LEVEL --- 0-24


SELECT * FROM SYSMESSAGES
WHERE severity=15

SELECT distinct severity
FROM SYSMESSAGES 
order by severity


0
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT DATENAME(DW,GETDATE())

=> WRITE A PROGRAM TO INPUT EMPNO AND INCREMENT SALARY BY SPECIFIC AMOUNT BUT 
ON THURSDAY UPDATES ARE NOT ALLOWED

SELECT * FROM EMP1
1100.00
1000.00
2100.00

DECLARE @ENO INT, @AMT MONEY
SET @ENO = 7876
SET @AMT = 1000

IF DATENAME(DW,GETDATE()) = 'Tuesday'
RAISERROR('ON Tuesday UPDATES ARE NOT ALLOWED', 15,1)
ELSE
UPDATE EMP1 SET SAL = SAL + @AMT  WHERE EMPNO = @ENO


==========================================================================