Using error functions in a nested TRY...CATCH

You received some new electric bikes in your store, so you need to update the stock.

You want to register that you received 2 Trek Powerfly 5 - 2018 bikes with a price of $3499.99 each, and 3 New Power K- 2018 bikes at $1999.99 each.

You try to insert the products in the database because you think they are new models. However, you forgot you already have the first one in stock. Luckily, the products table has a constraint requiring every product name to be unique.

You prepare a script controlling possible errors in the insertions. You also want to insert possible errors in a table called errors, and, if something fails when inserting the error, show the error number and error message.

This exercise is part of the course

Transactions and Error Handling in SQL Server

View Course

Exercise instructions

Surround the error handling with a CATCH block.
Insert 'Error inserting a product' in the errors table and surround this insertion with another TRY block.
Surround the nested error handling with another CATCH block.
Select the error line and the error message in the inner CATCH block.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

BEGIN TRY
    INSERT INTO products (product_name, stock, price) 
    VALUES	('Trek Powerfly 5 - 2018', 2, 3499.99),   		
    		('New Power K- 2018', 3, 1999.99)		
END TRY
-- Set up the outer CATCH block
___ ___
	SELECT 'An error occurred inserting the product!';
    -- Set up the inner TRY block
    ___ ___
    	-- Insert the error
    	INSERT INTO ___ 
        	VALUES ('Error inserting a product');
    ___ ___    
    -- Set up the inner CATCH block
    ___ ___
    	-- Show number and message error
    	SELECT 
        	___ AS line,	   
			___ AS message; 
    ___ ___    
___ ___

Edit and Run Code