How do I add an error message to my stored procedure when 0 rows are affected?

Question

I am trying to learn how to use transactions and error handling. Got stuck with custom error messages.

The stored procedure generates badges for AdventureWorks employees. The rule is for each employee to have only one valid badge at a time. If a badge is lost, a new badge is issued while the old badge is invalidated. This part of a code seems to work well so far.

Where I really got stuck was generating an error message advising that no badge was issued when an invalid employee ID is entered.

Also, just need a second opinion. I tried to set @@TRANSCOUNT and ROLLBACK TRANSACTION to make sure it does not mess with existing badges (e.g. invaliding a badge while not issuing a new one). But, I have a feeling that it is really not needed in this procedure. What do you think?

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;

Also, just in case if you need it for more context, presented below is dbo.Badges that I've created:

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

Firstly, the @@ROWCOUNT check needs to be on the line immediately following the one you want to check for. Secondly, you want it within the TRY

I would recommend not using any of this error-handling code. Among other issues: it uses PRINT which is only meant for debugging. It wipes the original error without rethrowing, and can also only print a single error, not multiple.

You also must have SET XACT_ABORT ON because you have a transaction. And once you do that, none of the error handling is actually necessary. All you want is to conditionally throw an error back to the client, and clean up the transaction. THROW will do the former, XACT_ABORT the latter.

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;

This procedure is guaranteed to clean up after itself without leaving open transactions, even though it has no error-handling, because XACT_ABORT will clean up.

You may also want to add a HOLDLOCK hint if you are worried about concurrency.

Answer 2

Why you are doing the work and then rolling back the work ?

A Simple initial check, will make the code easier to understand. Having TRY CATCH block will be helpful, in case any other transactional consistency issues occur and rolling back of the transaction is required.

If you have nested transactions, I would suggest you to refer to pattern by @gbn: Nested stored procedures containing TRY CATCH ROLLBACK pattern?. I have utilized his template below, for single transaction.

So, what you have to do is:

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