That's what I also did yesterday. The only problem is that I'd like this message to be thrown to the client. In my catch logic I have a different message + SQL Server error message. I think it would work for my purpose.

>Why should you use such block?
>Just raise error there:
>

>IF EXISTS (SELECT SpouseID from AdultInfo where PersonID = @SpouseID AND
>          (SpouseID IS NOT NULL AND SpouseID <> @PersonID and SpouseID >0))
>    BEGIN
>        RAISERROR ('Something happens that is not right' ,
>                   16, 1)
>    END
>

>
>>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.