In the SQL Server criminal justice system, uncaught errors are considered to be especially heinous.  In your code, the TRY-CATCH along with RAISERROR or THROW are members of an elite squad that can be used to trap errors, trap invalid values, or trap user mistakes before it even gets to the database. This is their story.

Law and Order: SQL Error-Trapping Unit

*DUN DUN*

Overview

By the end of this, we are going to use TRY-CATCH and use either THROW or RAISERROR to put together a structure where not only system-generated errors are being raised, but also errors you are trying to trap for validation. We are also going to use BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION to keep everything happening in our block of code either committing or not committing to the database all together.

TRY-CATCHes

TRY-CATCH is used by SQL, C#, Java, and other languages to try a piece of code, then catch the exceptions when that code doesn’t work. As is common in T-SQL, we begin a block with BEGIN and end a block with END. The TRY block and CATCH block both have to be included – you can’t just have TRY or just have CATCH.

BEGIN TRY
  --WHERE WE TRY TO DO STUFF
END TRY
BEGIN CATCH
  --WHERE WE CATCH THE BAD GUYS
END CATCH

RAISERRORs

RAISERRORs are used in SQL Server to … well, they raise errors. Think of this as they are raising the red flag, and as long as the Severity is above 10, the flag will be red. 

The second value, the number 16, is the severity. Any severity 10 or under isn’t considered an error. Try changing the 16 to 10. 

The last number in that message is the state. State is a tinyint, but otherwise, the value is up to you. You can use it to refer to certain conditions for the same error message.

The error number defaults to 50000. You have to add error messages to sys.messages to use anything else.

RAISERROR('This is an error!',16,1)

THROWs

THROWs are the new RAISERRORs.  MS would rather us use them. The first number, 50000 in this case, is the error number. You can simply type in and use any number here for an error between 50000 and 2147483647. You don’t need to add anything to sys.messages to use a different error message.

NOTE: The line of code before the THROW must end with a semicolon.

THROW 21777555, 
 'This is an error!', 
 2;
THROW 50000, 
 'This is an error!',
 1;

Forming the SQL Error-Trapping Unit*

\* Note: This is not the actual name. See intro to get Law and Order: SETU joke.

TRANSACTION

Notice in the code below:
After our DECLAREs and BEGIN, we have BEGIN TRANSACTION.
At the end, we have a COMMIT TRANSACTION.
In the CATCH, we have a ROLLBACK TRANSACTION.
This is what will keep invalid things from happening in the database. Putting code inside an explicitly declared transaction like this is more important in code where you have several updates, inserts, and deletes happening one after the other. If we only manage to get through half of it, we don’t want just half of the code to be in the database – we want all or nothing. The way this is set up, it is either all going to work, or it is all going to fail. Keeping it all a single unit like that is called an atomic transaction.

@step Variable

I like to have text for each section with a description of what comes next. You’ll see I concatenate that into the error messages. It allows me to easily jump to where the problem is when there’s a problem. You can use the state part of the RAISERROR or THROW to indicate this, but I like the text version more.

RAISERROR Version

For RAISERROR, we will use the RAISERROR in the TRY section to raise specific errors that we want raise to trap invalid values, etc. The CATCH section will catch any other errors that could happen due to the varied and wondrous reasons for errors.

Notice in the CATCH section, the RAISERROR is created similarly to how we were using it in our own conditions. Difference is, it is using system functions to return ERROR_MESSAGE(), ERROR_STATE(), and ERROR_SEVERITY() (lines 49 – 51). When we explicitly use RAISERROR with our code, we set those values. When the code is caught erroring for other reasons, SQL Server is setting them based on its own errors.

Try the following out on your own, changing the values as shown and running the code to see the results:

  1. val1 = 0, val2 = 1 – Errors because val1 = 0. We have an IF statement looking for val1 to be 0 because we do not want that (line 20). It’s not a valid entry according to our own rules, so we RAISERROR (line 24).
  2. val1 = 1, val2 = 1 – Errors because val1 = val2. We have an IF statement looking for val1 and val2 to be equal because we do not want that (line 28). It’s not a valid entry according to our own rules, so we RAISERROR (line 34).
  3. val1 = 1, val2 = 3 – Errors because divide by 0 (lines 39 – 42). Notice that there is no explicit check or raise error for this – this is 100% being handled by the CATCH block. Even though the table is being inserted into by that point (line 37), the INSERT is being rolled back in the CATCH block (line 47).
  4. val1 = 1, val2 = 2 – Works and the results are inserted (line 37), committed (lines 56 – 57), and the values are displayed in a table (line 59).
DECLARE @val1 int = 1, 
		@val2 int = 2,
		@step NVARCHAR(100) = 'Initialize',
		@MyError NVARCHAR(4000),
        
        --USED FOR ERROR MESSAGE LATER
		@ErrorMessage  NVARCHAR(4000),
		@ErrorNumber INT, 
		@ErrorSeverity INT, 
		@ErrorState    INT;

DECLARE @tbl TABLE (id1 INT, id2 INT);
 
BEGIN
	BEGIN TRANSACTION

	BEGIN TRY

			SET @step = 'Check Val1';
			IF @val1 = 0
			BEGIN
				SET @MyError = 'Step ' + @step + 
					': val1 is set to 0. Please use a different value.';
				RAISERROR(@MyError,16,1);
		    END

			SET @step = 'Compare Val1 and Val2';
			IF @val1 = @val2
			BEGIN
				SET @MyError = 'Step ' + 
					@step + ': val1 and val2 cannot be equal.' +
					' val1: ' + CAST(@val1 AS NVARCHAR) + 
					' val2: ' + CAST(@val2 AS NVARCHAR);
				RAISERROR(@MyError,16,1);
			END

			INSERT INTO @tbl (id1, id2) VALUES (@val1, @val2);
            
			if @val1 = 1 and @val2 = 3
			BEGIN
				SELECT 1/0
			END

	END TRY
	BEGIN CATCH
	    IF @@TRANCOUNT > 0  
	        ROLLBACK TRANSACTION; 

		SELECT	@ErrorMessage = ERROR_MESSAGE(),
				@ErrorState = ERROR_STATE(),
				@ErrorSeverity = ERROR_SEVERITY();

		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
	END CATCH

	IF @@TRANCOUNT > 0  
	    COMMIT TRANSACTION; 

	SELECT * FROM @tbl;

END

THROW Version

For THROW, we will use the THROW in the TRY section to raise specific errors that we want raise to trap invalid values, etc. The CATCH section will catch any other errors that could happen due to the varied and wondrous reasons for errors. Notice in the CATCH section, the THROW line is just the word THROW; (line 44).

Try the following out on your own, changing the values as shown and running the code to see the results:

  1. val1 = 0, val2 = 1 – Errors because val1 = 0. We have an IF statement looking for val1 to be 0 because we do not want that (line 14). It’s not a valid entry according to our own rules, so we THROW (line 18).
  2. val1 = 1, val2 = 1 – Errors because val1 = val2. We have an IF statement looking for val1 and val2 to be equal because we do not want that (line 22). It’s not a valid entry according to our own rules, so we THROW (line 28).
  3. val1 = 1, val2 = 3 – Errors because divide by 0 (lines 33 – 36). Notice that there is no explicit check or raise error for this – this is 100% being handled by the CATCH block. Even though the table is being inserted into by that point (line 31), the INSERT is being rolled back in the CATCH block (line 41 – 42).
  4. val1 = 1, val2 = 2 – Works and the results are inserted (line 31), committed (lines 47 – 48), and the values are displayed in a table (line 59).
DECLARE @val1 int = 1, 
		@val2 int = 2,
		@step NVARCHAR(100) = 'Initialize',
		@MyError NVARCHAR(4000),

DECLARE @tbl TABLE (id1 INT, id2 INT);
 
BEGIN
	BEGIN TRANSACTION

	BEGIN TRY

			SET @step = 'Check Val1';
			IF @val1 = 0
			BEGIN
				SET @MyError = 'Step ' + @step + 
					': val1 is set to 0. Please use a different value.';
				THROW 50001, @MyError,1;
		    END

			SET @step = 'Compare Val1 and Val2';
			IF @val1 = @val2
			BEGIN
				SET @MyError = 'Step ' + 
					@step + ': val1 and val2 cannot be equal.' +
					' val1: ' + CAST(@val1 AS NVARCHAR) + 
					' val2: ' + CAST(@val2 AS NVARCHAR);
				THROW 50002, @MyError,1;
			END

			INSERT INTO @tbl (id1, id2) VALUES (@val1, @val2);

			if @val1 = 1 and @val2 = 3
			BEGIN
				SELECT 1/0 AS val1is1val2is3
			END


	END TRY
	BEGIN CATCH
	    IF @@TRANCOUNT > 0  
	        ROLLBACK TRANSACTION; 

		THROW;
	END CATCH

	IF @@TRANCOUNT > 0  
	    COMMIT TRANSACTION; 

	SELECT * FROM @tbl;


END

Review

So now, we have used TRY-CATCH, THROW, and RAISERROR to trap not only system-generated errors, but our own errors based on our own rules for what flies and what doesn’t in our stored procedures.

Repeat after me: Never let @val1 = @val2! EVER! Unless of course it should.

We also used BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION to keep everything happening in our block of code either committing or not committing to the database all together as a single, atomic transaction.

Hope you enjoyed the article and hope it helps!

References:

Microsoft Docs – RAISERROR (Transact-SQL)

Microsoft Docs – TRY-CATCH (Transact-SQL)

Microsoft Docs – THROW (Transact-SQL)

Microsoft Docs – Transactions (Transact-SQL)