Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL with Try Catch Blocks with Throw

SQL Server Try Catch Usage

This article describes usage of T-SQL Try Catch Blocks with Throw or with regular SQL Error select statements using the AdventureWorks database in SQL Server 2014. The experiments will use an INSERT statement to THROW an exception via T-SQL back to the user. A stored procedure will also be created and used to demonstrate how to catch the OUTPUT error in a variable.

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.)

SQL Table Fields

Employee Table

BusinessEntity Table

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);

Error from THROW

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#.

SQL 'Finally' Statement

Using TRY CATCH THROW in an Stored Procedure

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

EmailAddress table in AdventureWorks

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;