Hi everybody,

I'm working on an SP where I try to update multiple tables in the transaction within the BEGIN TRY/CATCH block.

Now, in one particular case I'd like to generate a custom error message and then return this custom message to the client yet perform the cleanup logic (rollback, etc.) that is handled by the catch block.

Do you see the way to handle the problem?

E.g.

IF EXISTS (SELECT SpouseID from AdultInfo where PersonID = @SpouseID AND 
             (SpouseID IS NOT NULL AND SpouseID <> @PersonID and SpouseID >0))   
-- BTW, is using COALESCE(SpouseID,0)>) is better way here? I'm using it in other places in the same SP
  
             BEGIN    
               -- THROW an Error here
             END

and then the regular CATCH I have

BEGIN CATCH    
       DECLARE @ErrorSeverity int, @ErrorNumber int, 
            @ErrorMessage nvarchar(4000), @ErrorState int, 
            @ErrorLine int, @ErrorProc nvarchar(200)
        -- Grab error information from SQL functions
        SET @ErrorSeverity = ERROR_SEVERITY()
        SET @ErrorNumber = ERROR_NUMBER()
        SET @ErrorMessage = ERROR_MESSAGE()
        SET @ErrorState = ERROR_STATE()
        SET @ErrorLine = ERROR_LINE()
        SET @ErrorProc = ERROR_PROCEDURE()
		SET @ErrorMessage = 'Problem updating person''s information.' + CHAR(13) + 
            'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) +
                    ' in procedure: ' + @ErrorProc + 
                    ' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +
                    ' Error text: ' + @ErrorMessage 

        -- Not all errors generate an error state, to set to 1 if it's zero
        IF @ErrorState = 0
            SET @ErrorState = 1

        RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)

        -- If the error renders the transaction as uncommittable, we must rollback
        IF XACT_STATE() < 0
            ROLLBACK TRANSACTION
  END CATCH

I read the Help but it yet still unclear to me.

Thanks a lot in advance.