Scribd
Upload
5 views12 pages

Lesson 6 SQL NoSQL

The document covers managing relational and non-relational data using Transact-SQL, focusing on raising and handling errors, as well as implementing transactions. It explains the use of RAISERROR and THROW commands for error management, and the structure of TRY...CATCH blocks for error handling. Additionally, it discusses the concept of transactions, their ACID properties, and how to implement explicit transactions in SQL Server.

Uploaded by

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

Lesson 6 SQL NoSQL

The document covers managing relational and non-relational data using Transact-SQL, focusing on raising and handling errors, as well as implementing transactions. It explains the use of RAISERROR and THROW commands for error management, and the structure of TRY...CATCH blocks for error handling. Additionally, it discusses the concept of transactions, their ACID properties, and how to implement explicit transactions in SQL Server.

Uploaded by

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

Managing Relational and

Non-Relational Data

Transact-SQL - Programming with Transact-SQL

Post-Graduation in Enterprise Data Science & Analytics


Agenda

• Raising Errors

• Transactions

• Handling Errors

2
Raising Errors

• The RAISERROR Command


• Raise a user-defined error in sys.messages (SQL Server only)
• Raise an explicit error message, severity, and state (SQL Server and
Azure SQL Database)

RAISERROR (‘An Error Occurred‘, 16, 0);

• The THROW Command


• Replacement for RAISERROR
• Throw explicit error number, message, and state (severity is 16)
• Re-throw existing error

THROW 50001, ‘An Error Occurred‘, 0;

3
Demo
Demo 1 – Raising Errors

4
Catching and Handling Errors

• Use a TRY…CATCH Block


• Handle errors in the CATCH block
• Get error information:
DECLARE @Discount INT = 0;
• @@ERROR
BEGIN TRY
• ERROR_NUMBER() UPDATE Production.Product
SET Price = Price / @Discount
• ERROR_MESSAGE() END TRY
BEGIN CATCH
• ERROR_SEVERITY() PRINT ERROR_MESSAGE();
THROW 50001, ‘An error occurred’, 0;
• ERROR_STATE() END CATCH;
• ERROR_PROCEDURE()
• ERROR_LINE()

• Execute custom correction or logging code


• Re-throw the original error, or throw a custom error

5
Demo
Demo 2 – Catching and Handling Errors

6
Introduction to Transactions

• A transaction is a group of tasks defining a unit of work


• The entire unit must succeed or fail together—no partial
completion is permitted
--Two tasks that make up a unit of work
INSERT INTO Sales.Order ...
INSERT INTO Sales.OrderDetail ...

• Individual data modification statements are automatically


treated as standalone transactions
• SQL Server uses locking mechanisms and the transaction log to
support transactions

7
ACID Properties in DBMS

8
Implementing Explicit Transactions

• Use BEGIN TRANSACTION to start


a transaction
BEGIN TRY
• USE COMMIT TRANSACTION to BEGIN TRANSACTION
INSERT INTO Sales.Order…
complete a transaction INSERT INTO Sales.OrderDetail…
COMMIT TRANSACTION
• USE ROLLBACK TRANSACTION to END TRY
BEGIN CATCH
cancel a transaction IF @@TRANCOUNT > 0
BEGIN
• Or enable XACT_ABORT to ROLLBACK TRANSACTION
END
automatically rollback on error PRINT ERROR_MESSAGE();
THROW 50001, ‘An error occurred’, 0;
• Use @@TRANCOUNT and END CATCH;

XACT_STATE() to check
transaction status

9
Demo
Demo 3 – Implementing Transactions

10
Summary

• Raising Errors

• Transactions

• Handling Errors

11
Obrigado!

Morada: Campus de Campolide, 1070-312 Lisboa, Portugal


Tel: +351 213 828 610 | Fax: +351 213 828 611

You might also like