Implement error handling with T-SQL

In this lab, you’ll use T-SQL statements to test various error handling techniques in
the adventureworks database. For your reference, the following diagram shows the tables in
the database (you may need to resize the pane to see them clearly).

Write a basic TRY/CATCH construct

1. Start Azure Data Studio
2. From the Servers pane, double-click the AdventureWorks connection. A
green dot will appear when the connection is successful.
3. Right click the AdventureWorks connection and select New Query. A new
query window is displayed with a connection to the AdventureWorks
database.
4. The previous step will open a query screen that is connected to the TSQL
database.
5. In the query pane, type the following T-SQL code:
CodeCopy

SELECT CAST(N'Some text' AS int);

1. Select ⏵Run to run the code.

2. Notice the conversion error:

Result

Conversion failed when converting the nvarchar value ‘Some text’ to data type int.

3. Write a TRY/CATCH construct. Your T-SQL code should look like this:

CodeCopy

BEGIN TRY
SELECT CAST(N'Some text' AS int);
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH;

1. Run the modified code, and review the response. The results should include
no rows, and the Messages tab should include the text Error.

Display an error number and an error message

1. Right click the AdventureWorks connection and select New Query
2. Enter the following T-SQL code:

CodeCopy

DECLARE @num varchar(20) = '0';

BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH

END CATCH;

1. Select ⏵Run. Notice that you didn’t get an error because you used the
TRY/CATCH construct.
2. Modify the T-SQL code by adding two PRINT statements. The T-SQL code
should look like this:
CodeCopy

DECLARE @num varchar(20) = '0';

BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;

1. Run the modified code, and notice that an error is produced, but it’s one that
you defined.

Started executing query at line 1

Error Number: 8134

Error Message: Divide by zero error encountered.

2. Now change the value of the @num variable to look like this:
CodeCopy

DECLARE @num varchar(20) = 'A';

1. Run the modified code. Notice that you get a different error number and
message.

Started executing query at line 1

Error Message: Error converting data type varchar to numeric.

Error Number: 8114

2. Change the value of the @num variable to look like this:

CodeCopy

DECLARE @num varchar(20) = ' 1000000000';

1. Run the modified code. Notice that you get a different error number and
message.
 Started executing query at line 1

Error Number: 8115

Error Message: Arithmetic overflow error converting varchar to data type numeric.

Add conditional logic to a CATCH block

1. Modify the T-SQL code you used previously so it looks like this:

CodeCopy

DECLARE @num varchar(20) = 'A';

BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (245, 8114)
BEGIN
PRINT 'Handling conversion error...'
END
ELSE
BEGIN
PRINT 'Handling non-conversion error...';
END;

PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));

PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;

1. Run the modified code. You’ll see that message returned now contains more
information:

Started executing query at line 1

Handling conversion error…

Error Number: 8114

Error Message: Error converting data type varchar to numeric.

2. Change the value of the @num variable to look like this:

CodeCopy
DECLARE @num varchar(20) = '0';

1. Run the modified code. This produces a different type of error message:

Started executing query at line 1

Handling non-conversion error…

Error Number: 8134

Error Message: Divide by zero error encountered.

Create a stored procedure to display an error message

1. Right click the AdventureWorks connection and select New Query
2. Enter the following T-SQL code:

CodeCopy

CREATE PROCEDURE dbo.GetErrorInfo AS

PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(10));
PRINT 'Error State: ' + CAST(ERROR_STATE() AS varchar(10));
PRINT 'Error Line: ' + CAST(ERROR_LINE() AS varchar(10));
PRINT 'Error Proc: ' + COALESCE(ERROR_PROCEDURE(), 'Not within procedure');

1. Select ⏵Run. to run the code, which creates a stored procedure

named dbo.GetErrorInfo.
2. Return to the query that previously resulted in a “Divide by zero” error, and
modify it as follows:

CodeCopy

DECLARE @num varchar(20) = '0';

BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
EXECUTE dbo.GetErrorInfo;
END CATCH;

1. Run the code. This will trigger the stored procedure and display:
 Started executing query at line 1

Error Number: 8134

Error Message: Divide by zero error encountered.

Error Severity: 16

Error State: 1

Error Line: 4

Error Proc: Not within procedure

Rethrow the Existing Error Back to a Client

1. Modify the CATCH block of your code to include a THROW command, so that
your code looks like this:

CodeCopy

DECLARE @num varchar(20) = '0';

BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
EXECUTE dbo.GetErrorInfo;
THROW;
END CATCH;

1. Run the modified code. Here you’ll see that it executes the stored procedure,
and then throws the error message again (so a client application can catch
and process it).

Started executing query at line 1

Error Number: 8134

Error Message: Divide by zero error encountered.

Error Severity: 16
 Started executing query at line 1

Error State: 1

Error Line: 4

Error Proc: Not within procedure

Msg 8134, Level 16, State 1, Line 4

Divide by zero error encountered.

Add an Error Handling Routine

1. Modify your code to look like this:

CodeCopy

DECLARE @num varchar(20) = 'A';

BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
EXECUTE dbo.GetErrorInfo;

IF ERROR_NUMBER() = 8134
BEGIN
PRINT 'Handling devision by zero...';
END
ELSE
BEGIN
PRINT 'Throwing original error';
THROW;
END;

END CATCH;

1. Run the modified code As you’ll see, it executes the stored procedure to
display the error, identifies that it isn’t error number 8134, and throws the
error again.

Started executing query at line 1

Error Number: 8114

 Started executing query at line 1

Error Message: Error converting data type varchar to numeric.

Error Severity: 16

Error State: 5

Error Line: 5

Error Proc: Not within procedure

Throwing original error

Msg 8114, Level 16, State 5, Line 5

Error converting data type varchar to numeric.

Challenges
Now it’s time to try using what you’ve learned.
Tip: Try to determine the appropriate solutions for yourself. If you get stuck, suggested answers are
provided at the end of this lab.

Challenge 1: Catch errors and display only valid records

The marketing manager is using the following T-SQL query, but they are getting
unexpected results. They have asked you to make the code more resilient, to stop it
crashing and to not display duplicates when there is no data.
CodeCopy

DECLARE @customerID AS INT = 30110;

DECLARE @fname AS NVARCHAR(20);
DECLARE @lname AS NVARCHAR(30);
DECLARE @maxReturns AS INT = 1;

WHILE @maxReturns <= 10

BEGIN
SELECT @fname = FirstName, @lname = LastName FROM SalesLT.Customer
WHERE CustomerID = @CustomerID;
PRINT @fname + N' ' + @lname;
SET @maxReturns += 1;
SET @CustomerID += 1;
END;
 1. Catch the error
o Add a TRY .. CATCH block around the SELECT query.
2. Warn the user that an error has occurred
o Extend your TSQL code to display a warning to the user that their is an
error.
3. Only display valid customer records
o Extend the T-SQL using the @@ROWCOUNT > 0 check to only display
a result if the customer ID exists.

Challenge 2: Create a simple error display procedure

Error messages and error handling are essential for good code. Your manager has
asked you to develop a common error display procedure. Use this sample code as
your base.
CodeCopy

DECLARE @num varchar(20) = 'Challenge 2';

PRINT 'Casting: ' + CAST(@num AS numeric(10,4));

1. Catch the error

o Add a TRY…CATCH around the PRINT statement.
2. Create a stored procedure
o Create a stored procedure called dbo.DisplayErrorDetails. It should
display a title and the value
for ERROR_NUMBER, ERROR_MESSAGE and ERROR_SEVERITY.
3. Display the error information
o Use the stored procedure to display the error information when an
error occurs.

Challenge Solutions
This section contains suggested solutions for the challenge queries.

Challenge 1

1. Catch the error

CodeCopy

DECLARE @customerID AS INT = 30110;

DECLARE @fname AS NVARCHAR(20);
DECLARE @lname AS NVARCHAR(30);
DECLARE @maxReturns AS INT = 1;

WHILE @maxReturns <= 10

BEGIN
BEGIN TRY
SELECT @fname = FirstName, @lname = LastName FROM SalesLT.Customer
WHERE CustomerID = @CustomerID;

PRINT CAST(@customerID as NVARCHAR(20)) + N' ' + @fname + N' ' + @lname;

END TRY
BEGIN CATCH

END CATCH;

SET @maxReturns += 1;
SET @CustomerID += 1;
END;

1. Warn the user that an error has occurred

CodeCopy

DECLARE @customerID AS INT = 30110;

DECLARE @fname AS NVARCHAR(20);
DECLARE @lname AS NVARCHAR(30);
DECLARE @maxReturns AS INT = 1;

WHILE @maxReturns <= 10

BEGIN
BEGIN TRY
SELECT @fname = FirstName, @lname = LastName FROM SalesLT.Customer
WHERE CustomerID = @CustomerID;

PRINT CAST(@customerID as NVARCHAR(20)) + N' ' + @fname + N' ' +

@lname;
END TRY
BEGIN CATCH
PRINT 'Unable to run query'
END CATCH;

SET @maxReturns += 1;
SET @CustomerID += 1;
END;

1. Only display valid customer records

CodeCopy

DECLARE @customerID AS INT = 30110;

DECLARE @fname AS NVARCHAR(20);
DECLARE @lname AS NVARCHAR(30);
DECLARE @maxReturns AS INT = 1;

WHILE @maxReturns <= 10

BEGIN
 BEGIN TRY
SELECT @fname = FirstName, @lname = LastName FROM SalesLT.Customer
WHERE CustomerID = @CustomerID;

IF @@ROWCOUNT > 0
BEGIN
PRINT CAST(@customerID as NVARCHAR(20)) + N' ' + @fname + N' ' +
@lname;
END
END TRY
BEGIN CATCH
PRINT 'Unable to run query'
END CATCH

SET @maxReturns += 1;
SET @CustomerID += 1;
END;

Challenge 2

1. Catch the error

CodeCopy

DECLARE @num varchar(20) = 'Challenge 2';

BEGIN TRY
PRINT 'Casting: ' + CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH

END CATCH;

1. Create a stored procedure

CodeCopy

CREATE PROCEDURE dbo.DisplayErrorDetails AS

PRINT 'ERROR INFORMATION';
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(10));

1. Display the error information

CodeCopy

DECLARE @num varchar(20) = 'Challenge 2';

BEGIN TRY
PRINT 'Casting: ' + CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
EXECUTE dbo.DisplayErrorDetails;
END CATCH;