当0行受到影响时，如何将错误消息添加到存储过程中

Question

我正在努力学习如何使用事务和错误处理。被自定义错误消息卡住了。

存储过程为AdventureWorks员工生成徽章。规则是每个员工一次只能有一个有效的徽章。如果徽章丢失，将颁发一个新徽章，而旧徽章将失效。到目前为止，代码的这一部分似乎运行良好。

我真正陷入困境的地方是生成一条错误消息，通知当输入无效的员工ID时没有颁发徽章。

此外，只需要第二种意见。我尝试设置@@TRANSCOUNT和ROLLBACK TRANSACTION，以确保它不会干扰现有徽章(例如，使徽章失效而不颁发新徽章)。但是，我有一种感觉，在这个过程中真的不需要它。你觉得怎么样？

CREATE PROCEDURE dbo.spIssueNewID
@EmpID INTEGER
AS
BEGIN TRY
BEGIN TRANSACTION 
UPDATE dbo.Badges 
SET Validity = 'N' 
WHERE EmpID = @EmpID;
INSERT INTO dbo.Badges (EmpID, EmpName, EmpLastName)
SELECT BusinessEntityID, FirstName, LastName 
FROM AdventureWorks2016_EXT.person.person
WHERE BusinessEntityID = @EmpID;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ROWCOUNT = 0
BEGIN
PRINT 'No ID was issued'
END;

IF @@TRANCOUNT > 0
BEGIN 
ROLLBACK TRANSACTION
END
PRINT ERROR_MESSAGE();
THROW 50001,'An error occurred',0;
END CATCH;

此外，如果您需要更多上下文，下面是我创建的dbo.Badges：

CREATE TABLE dbo.Badges
(
ID uniqueidentifier NOT NULL DEFAULT NEWID(),
EmpID INTEGER NOT NULL 
REFERENCES Person.Person (BusinessEntityID),
EmpName nvarchar(50) NOT NULL,
EmpLastName nvarchar(50) NOT NULL,
IssueDate date DEFAULT GETDATE(),
Validity char(1) DEFAULT 'Y',
CONSTRAINT ID_status CHECK (Validity IN ('Y', 'N'))
);

Answer 1

首先，@@ROWCOUNT检查需要立即在您要检查的行之后的行上。其次，您希望它在TRY中

我建议不要使用任何此类错误处理代码。除其他问题外：它使用了仅用于调试的PRINT。它在不重新考虑的情况下擦除原始错误，并且只能打印单个错误，而不能打印多个错误。

您还必须具有SET XACT_ABORT ON，因为您有事务。一旦您这样做了，就不需要任何错误处理了。您只需要有条件地将错误返回给客户端，并清理事务。THROW将执行前者，XACT_ABORT将执行后者。

CREATE OR ALTER PROCEDURE dbo.spIssueNewID
@EmpID INTEGER
AS
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRANSACTION;
UPDATE dbo.Badges  -- possibly add WITH (HOLDLOCK) here
SET Validity = 'N'
WHERE EmpID = @EmpID;
INSERT INTO dbo.Badges (EmpID, EmpName, EmpLastName)
SELECT BusinessEntityID, FirstName, LastName
FROM person.person
WHERE BusinessEntityID = @EmpID;
IF @@ROWCOUNT = 0
THROW 50001, 'No ID was issued', 0;
COMMIT TRANSACTION;

此过程被保证在不留下未结事务的情况下自行清理，即使它没有错误处理，因为XACT_ABORT将进行清理。

如果您担心并发性，您可能还需要添加HOLDLOCK提示。

Answer 2

为什么要先做工作，然后再回滚工作？

一个简单的初始检查，将使代码更容易理解。如果出现任何其他事务一致性问题并且需要回滚事务，那么TRY CATCH块将非常有用。

如果您有嵌套的事务，我建议您参考@gbn的模式：包含TRY CATCH ROLLBACK模式的嵌套存储过程？。我使用了他下面的模板，用于单笔交易。

所以，你要做的是：

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
IF EXISTS(
SELECT 1 from dbo.Badges
WHERE EmpID= @EmpID)
BEGIN
BEGIN TRANSACTION
UPDATE dbo.Badges SET Validity ='N' WHERE EmpID=@EmpID;
INSERT INTO dbo.Badges (EmpID, EmpName, EmpLastName)
SELECT BusinessEntityID, FirstName, LastName from AdventureWorks2016_EXT.person.person
WHERE BusinessEntityID = @EmpID;
COMMIT TRANSACTION
END
ELSE
BEGIN
THROW 50001, 'Batch was never issued for the EmployeeId', 0; 
END
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 
ROLLBACK TRANSACTION;
THROW;
--before SQL Server 2012 use 
--RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO