Error & Exception in SQL

use demostart


if object_id('ValidateProc') is not null
drop proc ValidateProc
go

create procedure ValidateProc
as
select * from  NonexistanceTable

GO

exec ValidateProc

-------------------------
begin try
exec ValidateProc
end try
begin catch
select error_number() 'Error Number',ERROR_MESSAGE ()'Error Message',
ERROR_PROCEDURe(),ERROR_SEVERITY ()
end catch

------------------------------------------------------------------
--Working with Raiserroe
Alter procedure ValidateProc
as
declare @x int, @y int
set @x=10
set @y=0
if @y=0
print @x/@y
------------------
begin try
exec ValidateProc
end try

begin catch
select ERROR_NUMBER (),ERROR_MESSAGE ()
end catch

----------------------------------------------------

exec sp_addmessage 50001,14,'Divide by zero error'
alter procedure ValidateProc
as
declare @x int, @y int
set @x=10
set @y=0
if @y=0
Raiserror (50001,-1,-1)
print @x/@y

begin try
exec ValidateProc
end try

begin catch
select ERROR_NUMBER (),ERROR_MESSAGE ()
end catch
---------------------------------------------------------

--comparing raiserroe and throw

USE demostart;
GO
CREATE TABLE dbo.TestTable
(
  column_1 int NOT NULL PRIMARY KEY,
  column_2 int NULL
);

BEGIN TRY
  TRUNCATE TABLE dbo.TestTable;
  INSERT dbo.TestTable VALUES(1, 1);
  PRINT 'First Insert';
  -- Msg 2627, Level 14, State 1 - Violation of PRIMARY KEY constraint
  INSERT dbo.TestTable VALUES(1, 1);
  PRINT 'Second Insert';
END TRY
BEGIN CATCH
  DECLARE @ErrorNumber INT = ERROR_NUMBER();
  RAISERROR(@ErrorNumber, 16, 1)
END CATCH;

----The raiserror itself will give an error because a system defined cannot be raise by raiserror function
--You can do the following

BEGIN TRY
  TRUNCATE TABLE dbo.TestTable;
  INSERT dbo.TestTable VALUES(1, 1);
  PRINT 'First Insert';
  -- Msg 2627, Level 14, State 1 - Violation of PRIMARY KEY constraint
  INSERT dbo.TestTable VALUES(1, 1);
  PRINT 'Second Insert';
END TRY
BEGIN CATCH
  DECLARE @ErrorNumber INT = ERROR_NUMBER();
  DECLARE @ErrorMessage NVARCHAR(1000) = ERROR_MESSAGE()
  RAISERROR('Error Number-%d : Error Message-%s', 16, 1,
  @ErrorNumber, @ErrorMessage)
END CATCH;
-------------------------------------