Post reply

after begin ... raise error'

  • November 23, 2011 at 12:28 pm

    #252342

    We have a stored procedure. In the code I subtract part of it as below:

    IF @ProcessNm = 'BatchAssignment' AND @ProcessType = 'Post'

    BEGIN

    SELECT @existingProcessID = ProcessID

    FROM dbo.appProcess

    WHERE ProcessNm = @ProcessNm

    AND ProcessType = @ProcessType

    AND SchoolYear = @SchoolYear

    AND ProcessStatus = 'Complete'

    IF @existingProcessID IS NOT NULL

    BEGIN

    RAISERROR('Complete %s %s process (ProcessID = %d) exists in appProcess table for the %d SchoolYear.',11,0,@ProcessNm,@ProcessType,@existingProcessID,@SchoolYear)

    END

    END

    INSERT INTO [dbo].[appProcess] (

    [SchoolYear],

    [ProcessType],

    [ProcessNm],

    [ProcessStartDt],

    [ProcessEndDt],

    [ProcessStatus],

    ...

    My question is : you can see there is a begin ..raise error ...end, if we have the error happened, will the sproc exit, and does the insert part still taken place or not?

    Thanks

  • November 23, 2011 at 1:10 pm

    #1412448

    RAISERROR does not abort the batch. The process will continue after a RAISERROR unless the RAISERROR is withing a TRY...CATCH block. In a TRY...CATCH block RAISERROR sends it to the CATCH block. You need to explicitly ROLLBACK and RETURN in order to stop processing after a RAISERROR outside a TRY...CATCH.

    You could also use a severity level high enough to terminate the batch/connection.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 23, 2011 at 3:54 pm

    #1412507

    Thanks, it helps a lot

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply