Review the Data Tables to Set up the Try Catch Throw Block Code

Review tables BusinessEntity and Employee for the data and required data for Insert (Fields that have constraints such as Not Null, etc.)

SSMS TRY CATCH Block with an Insert statement

Test TRY CATCH in T-SQL with a normal Select ErrorNumber, ErrorMessage statement in the Catch Block following a TRY on the INSERT Statement.

BEGIN TRY
 Declare @busID int;
 INSERT INTO Person.BusinessEntity (rowguid, ModifiedDate) VALUES (NEWID(), '2017-05-01');
 set @busID = SCOPE_IDENTITY();
 INSERT INTO HumanResources.Employee (BusinessEntityID, LoginID, NationalIDNumber, JobTitle,
   BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours,
   CurrentFlag, rowguid, ModifiedDate)
   Values (@busID, 'adventure-works\terri0', '29500000', 'Design Engineer', '1977-01-01', 'M', 'F',
   '2015-12-31', 1, 80, 60, 1, newid(), '2017-05-01');
 END TRY
 BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() as ErrorMessage;
 END CATCH;
--no finally statement, but you can still execute code
 print 'End of execution.'

This statement failed as the HumanResources.Employee table will not accept a duplicate value for the Login ID, in this case it is adventure-works\terri0.

This sample uses the same statements as the first example only using Throw this time.

 BEGIN TRY
  Declare @busID int;
  INSERT INTO Person.BusinessEntity (rowguid, ModifiedDate) VALUES (NEWID(), '');
  set @busID = SCOPE_IDENTITY();
  INSERT INTO HumanResources.Employee (BusinessEntityID, LoginID, NationalIDNumber, JobTitle,
  BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours,
   CurrentFlag, rowguid, ModifiedDate)
  Values (@busID, 'adventure-works\terri0', '29500000', 'Design Engineer', '1977-01-01', 'M', 'F',
   '2015-12-31', 1, 80, 60, 1, newid(), '2017-05-01');
 END TRY
 BEGIN CATCH
  THROW;
 END CATCH;
print 'Code execution complete. ' + convert(varchar, getdate(), 0);

If the data entered is valid (by entering a different Login ID (Email), the last Print command is executed - similar to a Finally statement in C#.

Using TRY CATCH THROW in an Stored Procedure

Verify the data in the Person.EmailAddress table with a Select statement:
select * from Person.EmailAddress;

Create the Stored Procedure to INSERT into the table using TRY CATCH and an OUTPUT variable - for Valid data, the THROW will generate its own output to the user.

CREATE PROC Person.usp_EmailInsert
 ( @busID int=NULL,
   @email nvarchar(256)=NULL,
   @msg varchar(255) OUTPUT
  )
 AS

 BEGIN TRY
  INSERT INTO Person.EmailAddress(BusinessEntityID, EmailAddress, rowguid, ModifiedDate) VALUES
  (@busID, @email, newID(), getdate());
 END TRY
 BEGIN CATCH
  THROW;
 END CATCH
 Set @msg = 'Code execution complete. ' + convert(varchar, getdate(), 0);
 GO

In an SSMS Query window, write T-SQL to verify the output after Invalid data has been entered.

--verify an Invalid one for an error message
declare @message varchar(255);
EXEC Person.usp_EmailInsert 20782, 'terri0@adventure-works.com', @msg = @message OUTPUT;
select @message;

In an SSMS Query window, write T-SQL to verify the output after a valid entry has been input.

--verify Valid input for the final message
declare @message varchar(255);
EXEC Person.usp_EmailInsert 2, 'terri0@adventure-works.com', @msg = @message OUTPUT;
select @message;