RAISERROR Dosn't Work Inside CATCH With ROLLBACK TRANSACTION

Question

I created a Stored Procedure to Insert Into 2 Table With Transaction to make sure That Both Inserts Done and I Used TRY and CATCH to Handle The Errors .. The Problem Is In The Catch Statement I Put ROLLBACK TRANS and RAISERROR The RoLLBACK Works But The Procedure Dose not RAISERROR Here is The Code

ALTER PROC SP_InsertPlot
@PlotName nvarchar(50),
@GrossArea int,
@SectorName Nvarchar(50),
@PlotYear int,
@OwnerName Nvarchar(50),
@Remarks text,
@NumberOfPlants INT,
@NetArea INT,
@Category Nvarchar(50),
@Type Nvarchar(50),
@Variety Nvarchar(50),
@RootStock Nvarchar(50),
@PlantDistance Decimal(18,2)
AS
BEGIN
DECLARE @PlotID INT
SET @PlotID = (SELECT ISNULL(MAX(PlotID),0) FROM Plots) + 1 

DECLARE @SectorID INT 
SET @SectorID = (SELECT SectorID FROM Sectors WHERE SectorName = @SectorName)

DECLARE @OwnerID INT 
SET @OwnerID = ( SELECT OwnerID FROM Owners WHERE OwnerName = @OwnerName)

DECLARE @CategoryID INT
SET @CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName = @Category)

DECLARE @TypeID INT 
SET @TypeID = (SELECT TypeID FROM Types WHERE TypeName = @Type)

DECLARE @VarietyID INT
SET @VarietyID = (SELECT VarietyID FROM Varieties WHERE VarietyName = @Variety)

DECLARE @RootStockID INT 
SET @RootStockID = (SELECT RootStockID FROM RootStocks WHERE RootStockName = @RootStock)

DECLARE @PlotDescID INT
SET @PlotDescID = (SELECT ISNULL(MAX(PlotDescID),0) FROM PlotDescriptionByYear) + 1

BEGIN TRY
    SET XACT_ABORT ON
    SET NOCOUNT ON

    IF(SELECT Count(*) FROM Plots WHERE PlotName = @PlotName) = 0
    BEGIN
    BEGIN TRANSACTION
    INSERT INTO Plots (PlotID,PlotName,GrossArea,SectorID,PlantYear,OnwerID,Remarks)
        VALUES(@PlotID,@PlotName,@GrossArea,@SectorID,@PlotYear,@OwnerID,@Remarks)


    INSERT INTO PlotDescriptionByYear (PlotDescID, PlantYear, NumberOfPlants,PlotID,NetArea,CategoryID,TypeID,VarietyID,RootStockID,PlantDistance) 
        VALUES(@PlotDescID,YEAR(GETDATE()),@NumberOfPlants,@PlotID -1,@NetArea,@CategoryID,@TypeID,@VarietyID,@RootStockID,@PlantDistance)
    COMMIT TRANSACTION
    END

END TRY

BEGIN CATCH
        IF(XACT_STATE())= -1
        BEGIN
        ROLLBACK TRANSACTION
        RAISERROR('This Plot Is Already Exists !!',11,1)
        END
END CATCH

END

By the way i tried to change the Severity and I tried @@TRANCOUNT instead of XACT_STATE and the Same Problem Happens Which is When I Exec Proc and Pass an Existing Data To The Parameters The Transaction Roll back and did not rise the error

Rafał Wojtaszek Rafał Wojtaszek · Accepted Answer · 2015-10-07T17:38:52

0

votes

Change IF(XACT_STATE())= -1 to IF(XACT_STATE()) <> 0 and your problem will be done.

RAISERROR Dosn't Work Inside CATCH With ROLLBACK TRANSACTION

1 Answers