Scribd
Upload
135 views9 pages

22 - Transactions and Error Handling in SQL Server

This document provides examples of using error handling, transactions, and try/catch blocks in SQL Server. It demonstrates how to add error handling to scripts using try/catch blocks, insert errors into an errors table, and return error information. Examples also show nesting try/catch blocks, using transactions with savepoints and rollback, and customizing error messages with RAISERROR and THROW.

Uploaded by

massyweb
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
135 views9 pages

22 - Transactions and Error Handling in SQL Server

This document provides examples of using error handling, transactions, and try/catch blocks in SQL Server. It demonstrates how to add error handling to scripts using try/catch blocks, insert errors into an errors table, and return error information. Examples also show nesting try/catch blocks, using transactions with savepoints and rollback, and customizing error messages with RAISERROR and THROW.

Uploaded by

massyweb
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 9

/*

Starting with error handling


*/

-- error-handling script

-- Set up the TRY block


BEGIN TRY
-- Add the constraint
ALTER TABLE products
ADD CONSTRAINT CHK_Stock CHECK (stock >= 0);
END TRY
-- Set up the CATCH block
BEGIN CATCH
SELECT 'An error occurred!';
END CATCH

-- Nesting TRY...CATCH constructs

-- Set up the first TRY block


BEGIN TRY
INSERT INTO buyers (first_name, last_name, email, phone)
VALUES ('Peter', 'Thompson', 'peterthomson@mail.com', '555000100');
END TRY
-- Set up the first CATCH block
BEGIN CATCH
SELECT 'An error occurred inserting the buyer! You are in the first CATCH
block';
-- Set up the nested TRY block
BEGIN TRY
INSERT INTO errors
VALUES ('Error inserting a buyer');
SELECT 'Error inserted correctly!';
END TRY
-- Set up the nested CATCH block
BEGIN CATCH
SELECT 'An error occurred inserting the error! You are in the nested CATCH
block';
END CATCH
END CATCH

-- Correcting compilation errors

BEGIN TRY
INSERT INTO products (product_name, stock, price)
VALUES ('Sun Bicycles ElectroLite - 2017', 10, 1559.99);
END TRY
BEGIN CATCH
SELECT 'An error occurred inserting the product!';
BEGIN TRY
INSERT INTO errors
VALUES ('Error inserting a product');
END TRY
BEGIN CATCH
SELECT 'An error occurred inserting the error!';
END CATCH
END CATCH
-- Using error functions

-- Set up the TRY block


BEGIN TRY
SELECT 'Total: ' + SUM(price * quantity) AS total
FROM orders
END TRY
-- Set up the CATCH block
BEGIN CATCH
-- Show error information.
SELECT ERROR_NUMBER() AS number,
ERROR_SEVERITY() AS severity_level,
ERROR_STATE() AS state,
ERROR_LINE() AS line,
ERROR_MESSAGE() AS message;
END CATCH

-- Using error functions in a nested TRY...CATCH

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
BEGIN CATCH
SELECT 'An error occurred inserting the product!';
-- Set up the inner TRY block
BEGIN TRY
-- Insert the error
INSERT INTO errore
VALUES ('Error inserting a product');
END TRY
-- Set up the inner CATCH block
BEGIN CATCH
-- Show number and message error
SELECT
ERROR_LINE() AS line,
ERROR_MESSAGE() AS message;
END CATCH
END CATCH

/*
Raising, throwing and customizing your errors
*/

-- CATCHING the RAISERROR

BEGIN TRY
DECLARE @product_id INT = 5;
IF NOT EXISTS (SELECT * FROM products WHERE product_id = @product_id)
RAISERROR('No product with id %d.', 11, 1, @product_id);
ELSE
SELECT * FROM products WHERE product_id = @product_id;
END TRY
-- Catch the error
BEGIN CATCH
-- Select the error message
SELECT ERROR_MESSAGE();
END CATCH

-- THROW without parameters

CREATE PROCEDURE insert_product


@product_name VARCHAR(50),
@stock INT,
@price DECIMAL

AS

BEGIN TRY
INSERT INTO products (product_name, stock, price)
VALUES (@product_name, @stock, @price);
END TRY
-- Set up the CATCH block
BEGIN CATCH
-- Insert the error and end the statement with a semicolon
INSERT INTO errors VALUES ('Error inserting a product');
-- Re-throw the error
THROW;
END CATCH

-- Executing a stored procedure that throws an error

BEGIN TRY
-- Execute the stored procedure
EXEC insert_product
-- Set the values for the parameters
@product_name = 'Super bike',
@stock = 3,
@price = 499.99;
END TRY
-- Set up the CATCH block
BEGIN CATCH
-- Select the error message
SELECT error_message();
END CATCH

-- THROW with parameters

-- Set @staff_id to 4
DECLARE @staff_id INT = 4;

IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = @staff_id)


-- Invoke the THROW statement with parameters
THROW 50001 , 'No staff member with such id', 1;
ELSE
SELECT * FROM staff WHERE staff_id = @staff_id
-- Concatenating the message

-- Set @first_name to 'Pedro'


DECLARE @first_name NVARCHAR(20) = 'Pedro';
-- Concat the message
DECLARE @my_message NVARCHAR(500) =
CONCAT('There is no staff member with ', @first_name, ' as the first name.');

IF NOT EXISTS (SELECT * FROM staff WHERE first_name = @first_name)


-- Throw the error
THROW 50000, @my_message, 1;

-- FORMATMESSAGE with message string

DECLARE @product_name AS NVARCHAR(50) = 'Trek CrossRip+ - 2018';


DECLARE @number_of_sold_bikes AS INT = 10;
DECLARE @current_stock INT;
-- Select the current stock
SELECT @current_stock = stock FROM products WHERE product_name = @product_name;
DECLARE @my_message NVARCHAR(500) =
-- Customize the message
FORMATMESSAGE('There are not enough %s bikes. You only have %d in stock.',
@product_name, @current_stock);

IF (@current_stock - @number_of_sold_bikes < 0)


-- Throw the error
THROW 50000, @my_message, 1;

-- FORMATMESSAGE with message number

EXEC sp_addmessage @msgnum = 50002, @severity = 16, @msgtext = 'There are not
enough %s bikes. You only have %d in stock.', @lang = N'us_english';

DECLARE @product_name AS NVARCHAR(50) = 'Trek CrossRip+ - 2018';


DECLARE @sold_bikes AS INT = 100;
DECLARE @current_stock INT;

SELECT @current_stock = stock FROM products WHERE product_name = @product_name;

DECLARE @my_message NVARCHAR(500) =


FORMATMESSAGE(50002, @product_name, @current_stock);

IF (@current_stock - @sold_bikes < 0)


THROW 50000, @my_message, 1;

-- FORMATMESSAGE with message number


EXEC sp_addmessage @msgnum = 50002, @severity = 16, @msgtext = 'There are not
enough %s bikes. You only have %d in stock.', @lang = N'us_english';

DECLARE @product_name AS NVARCHAR(50) = 'Trek CrossRip+ - 2018';


DECLARE @sold_bikes AS INT = 10;
DECLARE @current_stock INT;

SELECT @current_stock = stock FROM products WHERE product_name = @product_name;


DECLARE @my_message NVARCHAR(500) =
FORMATMESSAGE(50002, @product_name, @current_stock);

IF (@current_stock - @sold_bikes < 0)


THROW 50000, @my_message, 1;

/*
Transactions in SQL Server
*/

-- Correcting a transaction

BEGIN TRY
begin TRAN
UPDATE accounts SET current_balance = current_balance - 100 WHERE
account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());

UPDATE accounts SET current_balance = current_balance + 100 WHERE


account_id = 5;
INSERT INTO transactions VALUES (5, 100, GETDATE());
commit TRAN
END TRY
begin CATCH
rollback TRAN
END CATCH

-- Rolling back a transaction if there is an error

BEGIN TRY
-- Begin the transaction
BEGIN transaction;
UPDATE accounts SET current_balance = current_balance - 100 WHERE
account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());

UPDATE accounts SET current_balance = current_balance + 100 WHERE


account_id = 5;
-- Correct it
INSERT INTO transactions VALUES (500, 100, GETDATE());
-- Commit the transaction
commit TRAN;
END TRY
BEGIN CATCH
SELECT 'Rolling back the transaction';
-- Rollback the transaction
rollback;
END CATCH

-- Choosing when to commit or rollback a transaction

-- Begin the transaction


begin transaction;
UPDATE accounts set current_balance = current_balance + 100
WHERE current_balance < 5000;
-- Check number of affected rows
IF @@ROWCOUNT > 200
BEGIN
-- Rollback the transaction
rollback;
SELECT 'More accounts than expected. Rolling back';
END
ELSE
BEGIN
-- Commit the transaction
commit;
SELECT 'Updates commited';
END

-- Checking @@TRANCOUNT in a TRY...CATCH construct

BEGIN TRY
-- Begin the transaction
BEGIN TRAN;
-- Correct the mistake
UPDATE accounts SET current_balance = current_balance + 200
WHERE account_id = 10;
-- Check if there is a transaction
IF @@TRANCOUNT > 0
-- Commit the transaction
COMMIT TRAN;

SELECT * FROM accounts


WHERE account_id = 10;
END TRY
BEGIN CATCH
SELECT 'Rolling back the transaction';
-- Check if there is a transaction
IF @@TRANCOUNT > 0
-- Rollback the transaction
ROLLBACK TRAN;
END CATCH

-- Using savepoints

BEGIN TRAN;
-- Mark savepoint1
SAVE TRAN savepoint1;
INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com',
'555909090');

-- Mark savepoint2
SAVE TRAN savepoint2;
INSERT INTO customers VALUES ('Zack', 'Roberts', 'zackroberts@mail.com',
'555919191');

-- Rollback savepoint2
ROLLBACK TRAN savepoint2;
-- Rollback savepoint1
ROLLBACK TRAN savepoint1;
-- Mark savepoint3
SAVE TRAN savepoint3;
INSERT INTO customers VALUES ('Jeremy', 'Johnsson',
'jeremyjohnsson@mail.com', '555929292');
-- Commit the transaction
COMMIT TRAN;

-- XACT_ABORT and THROW

-- Use the appropriate setting


SET XACT_ABORT ON;
-- Begin the transaction
BEGIN TRAN;
UPDATE accounts set current_balance = current_balance - current_balance *
0.01 / 100
WHERE current_balance > 5000000;
IF @@ROWCOUNT <= 10
-- Throw the error
THROW 55000, 'Not enough wealthy customers!', 1;
ELSE
-- Commit the transaction
COMMIT TRAN;

-- Doomed transactions

-- Use the appropriate setting


SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com',
'555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', 'dylansmith@mail.com',
'555888999');
COMMIT TRAN;
END TRY
BEGIN CATCH
-- Check if there is an open transaction
IF XACT_STATE() <> 0
-- Rollback the transaction
ROLLBACK TRAN;
-- Select the message of the error
SELECT ERROR_MESSAGE() AS Error_message;
END CATCH

/*
Controlling the concurrency: Transaction isolation levels
*/

-- Using the READ UNCOMMITTED isolation level

-- Set the appropriate isolation level


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Select first_name, last_name, email and phone


SELECT
first_name,
last_name,
email,
phone
FROM customers;

-- Prevent dirty reads

-- Set the appropriate isolation level


SET transaction isolation level read committed

-- Count the accounts


SELECT count(*) AS number_of_accounts
FROM accounts
WHERE current_balance >= 50000;

-- Preventing non-repeatable reads

-- Set the appropriate isolation level


set transaction isolation level repeatable read

-- Begin a transaction
begin tran

SELECT * FROM customers;

-- some mathematical operations, don't care about them...

SELECT * FROM customers;

-- Commit the transaction


commit tran

-- Prevent phantom reads in a table

-- Set the appropriate isolation level


set transaction isolation level serializable

-- Begin a transaction
begin tran

SELECT * FROM customers;

-- After some mathematical operations, we selected information from the customers


table.
SELECT * FROM customers;

-- Commit the transaction


commit tran

-- Prevent phantom reads just in some rows

-- Set the appropriate isolation level


set transaction isolation level serializable
-- Begin a transaction
begin tran

-- Select customer_id between 1 and 10


SELECT *
FROM customers
where customer_id between 1 AND 10;

-- After completing some mathematical operation, select customer_id between 1 and


10
SELECT *
FROM customers
where customer_id between 1 AND 10;

-- Commit the transaction


commit tran

-- Avoid being blocked

SELECT *
-- Avoid being blocked
FROM transactions with (nolock)
WHERE account_id = 1

You might also like