Giới thiệu

Việc xử lý lỗi trong SQL Server hiện tại dễ dàng hơn thông qua các kỹ thuật khác nhau.

SQL Server đã giới thiệu các tùy chọn giúp người dùng có thể kiểm soát lỗi hiệu quả hơn

Thông thường, người dùng cuối sẽ không thể bắt được lỗi xuất hiện ở phía ứng dụng.

Vì vậy, SQL Server cung cấp câu lệnh TRY … CATCH giúp kiểm soát lỗi hiệu quả hơn ở tầng backend.

Một số các hàm hệ thống có thể in ra các thông tin liên quan về lỗi, giúp fix lỗi dễ dàng hơn.

Các kiểu lỗi (type of errors)

Lập trình viên T-SQL sẽ phải nhận thức được các kiểu lỗi xuất hiện khi làm việc với các câu lệnh SQL Server. Bước đầu tiên, là xác định kiểu lỗi, sau đó chỉ ra cách để xử lý lỗi đó.

Danh sách các kiểu lỗi

Syntax Errors

Lỗi cú pháp bắt gặp khi code sai cú pháp, SQL Server không thể hiểu. Những lỗi này được nhận diện bởi SQL Server trước khi bắt đầu thực thi tiến trình của khối lệnh T-SQL hoặc stored procedure.

Khi người dùng gõ các từ khóa hoặc các thao tác sai vì không nhớ cú pháp hợp lệ, code editor sẽ chỉ ra nó.

Nếu người dùng quên gõ từ khóa hoặc thao tác được yêu cầu để hoàn thiện câu lệnh, code editer sẽ thông báo lỗi khi nưgời dùng thực thi câu lệnh.

Syntax Errors (lỗi cú pháp) sẽ có thể dễ dàng nhận diện vì code editor sẽ chỉ ra, tuy nhiên, nếu sử dụng ứng dụng dòng lệnh, lỗi chỉ hiển thị sau khi người dùng thực thi câu lệnh.

Run-time Errors

Lỗi trong thời gian chạy xuất hiện khi ứng dụng cố gắng xử lý tác vụ mà không hỗ trợ bởi SQL Server hoặc hệ điều hành.

Một số trường hợp run-time errors có thể bắt gặp như sau:

• Xử lý phép tính toán chia cho 0
• Cố gắng thực thi đoạn mã không được định nghĩa rõ ràng. ( Ví dụ phía dưới có tình huống gián giá trị lớn hơn kiểu dữ liệu có thể biểu diễn).

Các tình huống phổ biến bắt gặp run-time error:

• Sử dụng stored procedure, hoặc function, hoặc trigger không có sẵn ( đã bị xóa).
• Cố gắng xử lý tác vụ hoạt động trên một object hoặc variable mà ko thể xử lý ( bảng bị xóa, không có trong CSDL …)
• Tình huống tràn Ram (stack overflow) khi ứng dụng quá tải
• Cố gắng xử lý các tác vụ với kiểu dữ liệu không tương thích.
• Sử dụng sai các câu lệnh điều kiện.

Triển khai xử lý lỗi

Trong quá trình phát triển phần mềm, một trong những điều quan trọng là cần quan tâm tới việc xử lý lỗi. Bằng một số cách, người dùng phải quan tâm tới việc xử lý các lỗi ngoại lệ khi thiết kết database. Các cơ chế xử lý khác nhau có thể được sử dụng.

• Khi thực thi các câu lệnh DML như INSERT,DELETE,UPDATE, người dùng có thể xử lý lỗi để đảm bảo chính xác đầu ra
• Khi transaction thất bại và user phải roll back transaction, một thông báo lỗi phù hợp cần được hiển thị cho người dùng.
• Khi làm việc với con trỏ trong SQL Server, người dùng có thể xử lý lỗi để đảm bảo chính xác kết quả.

Khối lệnh TRY … CATCH

Khối lệnh TRY … CATCH được sử dụng để triển khai xử lý lỗi ngoại lệ trong Transact SQL. Một hoặc nhiều caual ệnh T-SQl sẽ được bao bởi khối TR. Nếu một lỗi xuất hiện trong khối TRY, luồng điều khiển sẽ chuyển qua khối catch, bên trong khối CATCH có thể bao gồm 1 hoặc nhiều câu lệnh.

Cú pháp:

BEGIN TRY
{sql_statement | statement_block}
END TRY
BEGIN CATCH
[ {sql_statement | statement_block}]
END CATCH
[;]

Ví dụ:

BEGIN TRY
DECLARE @num int;
SELECT @num=217/0;
END TRY
BEGIN CATCH
PRINT 'Error occurred, unable to devide by 0'
END CATCH;

use AdventureWorks2019 
begin try
select * from Person.Person where BusinessEntityID = 
(select BusinessEntityID from HumanResources.Employee)
end try
begin catch
print 'Sub query tra ra cot, khong dung logic'
select * from Person.Person where BusinessEntityID in 
(select BusinessEntityID from HumanResources.Employee)
end catch

Thông tin lỗi

Giải pháp tốt nhất là hiển thị thông tin lỗi cùng với lỗi, để có thể giúp giải quyết lỗi một cách nhanh chóng và hiệu quả.

Để làm được việc này, các hàm hệ thống cần được sử dụng bên trong khối CATCH để tìm kiếm thông tin về lỗi được khởi tạo khi khối block được thực thi

Danh sách các hàm hệ thống cung cấp thông tin lỗi:

• ERROR_NUMBER() : trả về số lỗi.
• ERROR_SERVERITY() : trả về mức độ nghiêm trọng
• ERROR_STATE(): trả về số trạng thái của lỗi.
• ERROR_PROCEDURE(): trả về tên cảu trigger hoặc stored procedure gây ra lỗi.
• ERROR_LINE(): trả về số dòng gây ra lỗi
• ERROR_MESSAGE(): trả về văn bản hooàn thiện của lỗi, văn bản sẽ chưa các giá trị được cung cấp làm tham số như object names, độ dài.

Các hàm này sẽ trả về NULL nếu được gọi bên ngoài khối CATCH

Sử dụng TRY … CATCH với thông tin lỗi

USE AdventureWorks2019;
GO
BEGIN TRY
SELECT 217/0;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumer, ERROR_SEVERITY() AS ErrorSeverity, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Ví dụ stored proceudre chứa các hàm xử lý lỗi:

USE AdventureWorks2019;
GO
IF OBJECT_ID ('sp_ErrorInfo','P') IS NOT NULL
DROP PROCEDURE sp_ErrorInfo;
GO
CREATE PROCEDURE sp_ErrorInfo
AS 
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO
BEGIN TRY 
SELECT 217/0
END TRY
BEGIN CATCH
EXEC sp_ErrorInfo;
END CATCH

Sử dụng TRY … CATCH trong Transaction

USE AdventureWorks2019;
GO
BEGIN TRANSACTION;
BEGIN TRY
DELETE FROM Production.Product WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
GO

Uncomitable transaction

Nếu một lỗi được sinh ra từ khối TRY, nó là nguyên nhân của nó làm cho trạng thái của giao dịch hiện tại có hiệu lực và giao dịch được coi là giao dịch không được cam kết. Một uncomitable transaction chỉ thực hiện việc ROLLBACK TRANSACTION hoặc thao tác đọc.

Transaction sẽ không thực hiện bất kỳ câu lệnh Transact-SQL nào thực hiện giao dịch cam kết hoặc hoạt động ghi.

@@ERROR

Hàm @@ERROR trả về số lỗi của câu lệnh T-SQL cuối cùng được thực thi.

Cú pháp:

@@ERROR

Ví dụ:

USE AdventureWorks2019;
GO
BEGIN TRY
UPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4
WHERE BusinessEntityID = 1;
END TRY
BEGIN CATCH
IF @@ERROR = 547
PRINT N'Check constraint violation has occurred.';
END CATCH

RAISERROR

Câu lệnh RAISERROR bắt đầu xử lý lỗi cho một phiên và hiển thị thông báo lỗi ra ngoài.

RAISERROR có thể tham chiếu tới thông báo người dùng định nghĩa lưu trữ trong sys.message catalog view hoặc xây dựng một thông báo lỗi động ở run-time.

Cú pháp:

RAISERROR ( {msg_id | msg_str | @local_variable} 
{,serverity , state}
[,argument,[,...n]])
[WITH option [,...n]]

Trong đó:

• msg_id: xác định những thông báo lỗi người dùng tự định nghĩa lưu trữ trong sys.messages catalog view sử dụng sp_addmessage.
• msg_str: xác định những thông báo lỗi người dùng tự định nghĩa với định dạng. msg_str là một chuỗi các ký tự với các tùy chọn chuyển đổi. Các tham số có thể là d,i,o,s,x,X,u tương tự như string format trong hàm printf.

Danh cách sách giá trị cho custom options:

Value	Description
LOG	Ghi lại lỗi trong nhật ký lỗi của csdl và ứng dụng.
NOWAIT	Gửi thông báo trực tiếp cho máy khách
SETERROR	Gán ERROR_NUMBER và giá trị @@ERROR thành msg_id hoặc 5000 không không phân biệt mức độ nghiêm trọng

Các lỗi sau sẽ được trả về nếu RAISERROR được thực thi:

• Bên ngoài phạm vi của khối TRY
• Có độ nghiêm trọng 10 hoặc thấp hơn trong khối TRY
• Có độ nghiêm trọng 20 hoặc cao hơn chất dứt kết nối database.

Ví dụ sử dụng RAISERROR() để in ra một lỗi tùy chỉnh:

RAISERROR (N'This is an error message %s %d',10,1,N'serial number',23);
GO

Ví dụ sử dụng RAISERROR trong TRY CATCH:

BEGIN TRY 
RAISERROR('Raises Error in the TRY block',16,1);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE() , @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;

ERROR_SEVERITY

Hàm ERROR_SEVERITY trả về độ nghiêm trọng của lỗi trong khối catch của cấu trúc TRY CATCH.

Hàm này sẽ trả về null nếu như được gọi bên ngooài khối CATCH. ERROR _SEVERITY có thể đưojc gọi ở bất cứ đâu bên trong phạm vi khối CATCH.

Trong một khối CATCH lồng nhau, ERROR_SEVERITY sẽ trả về lỗi xác định phạm vi của khối catch nơi nó tham chiếu tới.

Cú pháp:

ERROR_SEVERITY()

Ví dụ:

BEGIN TRY
SELECT 217/0;
END TRY
BEGIN CATCH
SELECT ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
GO

ERROR_PROCEDURE

Hàm ERROR_PROCEDURE trả về tên trigger hoặc stored procedure nơi xảy ra lỗi của một cấu trúc TRY CATCH được thực thi.

Cú pháp:

ERROR_PROCEDURE()

Nó trả về kiểu dữ liệu nvarchar. Khi hàm được gọi trong khối CATCH, nó sẽ trả về tên của stored procedure nơi lỗi gặp phải.

ERROR_PROCEDURE có thể được gọi bất nơi nào trong phạm vi khối CATCH.

Ví dụ:

USE AdventureWorks2019;
GO
IF OBJECT_ID ('usp_Example','P') IS NOT NULL
DROP PROCEDURE usp_Example;
GO
CREATE PROCEDURE usp_Example AS
SELECT 217/0;
GO
BEGIN TRY
EXECUTE usp_Example;
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;
GO

ERROR_NUMBER

Hàm ERROR_NUMBER được gọi trong khối CATCH trả về số lỗi được tạo ra bởi khối CATCH khi cấu trúc TRY >> CATCH được thực thi.

Cú pháp:

ERROR_NUMBER()

ERROR_NUMBER trả về mã lỗi tương ứng không phân biệt bao nhiêu lần thực thi lỗi(tình huống có nhiều ngoại lệ ở các câu lệnh khác nhau) và nó thực thi bên trong phạm vi khối CATCH. Đây là sự khác biệt với @@ERROR vì nó chỉ trả về số lỗi bên trong ngay lập tức khi gặp lỗi, và phạm vi chỉ ở câu lệnh đầu tiên trong khối CATCH.

Ví dụ:

BEGIN TRY
SELECT 217/0;
SELECT 218/0;
END TRY
BEGIN CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

ERROR_MESSAGE

Hàm ERROR_MESSAGE trả về thông báo văn bản của lỗi gây ra trong cấu trong cấu trúc TRY CATCH

Cú pháp:

ERROR_LINE()

Khi hàm này được gọi trong khối CATCH, nó sẽ trả về toàn bộ văn bản của thông báo lỗi là nguyên nhân khối CATCH được thực thi.

Văn bản bao gồm các giá trị được cung cấp cho bất kỳ tham số nào có thể được thay thế như tên đối tượng, thời gian hoặc độ dài.

Ví dụ:

BEGIN TRY
SELECT 200/0;
END TRY
BEGIN CATCH 
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

ERROR_LINE

Hàm ERROR_LINE trả về số dòng gặp lỗi trong khối TRY CATCH.

Cú pháp:

ERROR_LINE()

Khi hàm được gọi trong khối CATCH, nó sẽ trả về số dòng mà gặp lỗi. Nếu lỗi bắt gặp bên trong trigger hoặc stored procedure, nó trả về số dòng trong trigger hoặc stored procedure đó. Tương tụ các hàm khác, hàm này trả về NULL nếu nó được gọi bên ngooài phạm vi khối CATCH.

Ví dụ:

BEGIN TRY
SELECT 217/0;
END TRY
BEGIN CATCH 
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
GO

Những lỗi không bị ảnh hưởng bởi cấu trúc TRY CATCH

Khối TRY CATCH sẽ không bắt các điều kiện sau:

• Thông tin của tin nhắn hoặc cảnh báo có severity 10 hoặc thấp hơn.
• Một lỗi có severity 20 hoặc cap hơn làm dừng tác vụ thực thi SQL Server Database Engine cho phiên.
• Nếu lỗi bắt gặp có severity 20 hoặc cao honw và kết nối database không bị gián đoạn, TRY CATCH sẽ xử lý lỗi
• Chú ý chẳng hạn như kết nối máy khách bị hỏng hoặc các yêu cầu bị ngắt quãng.
• Khi phiên kết thúc bằng câu lệnh KILL, thông qua admin hệ thống.

Các loại lỗi sau không được xử lý bởi một khối CATCH xảy ra ở cùng mức thực thi như của cấu trúc TRY CATCH:

• Lỗi biên dịch như lỗi cứ pháp.
• Các lỗi phát sinh trong quá trình biên dịch cấp câu lệnh, chẳng hạn như lỗi phân giải tên đối tượng ( tên đối tượng không tồn tại) xảy ra sau khi biên dịch do việc phân giải tên bị trì hoãn.

Ví dụ:

USE AdventureWorks2109;
GO
BEGIN TRY
SELECT * FROM Nonexistent;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH

THROW

Câu lệnh THROW đưa ra một ngoại lệ và chuyển quyền kiểm soát việc thực thi sang một khối CATCH của một cấu trúc TRY … CATCH.

Cú pháp:

THROW [{error_number | @local_varibale},
{message | @local_variable},
{state | @local_variable}]
[;]

Ví dụ:

USE tempdb;
GO
CREATE TABLE dbo.TestRethrow
(ID INT PRIMARY KEY);
BEGIN TRY
INSERT dbo.TestRethrow(ID) VALUES(1);
INSERT dbo.TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'In catch block.';
THROW
END CATCH;

Khi sử dụng THROW, mặc dù lệnh lỗi ràng buộc, nhưng lệnh PRINT bên trong khối CATCH vẫn được in, đây là vai trò của THROW trong SQL Server.