TRY and RAISERROR in T-SQL

Question

Having a small issue and wondering if I'm using these correctly.

In my SQL script is have

BEGIN TRY
    // check some information and if there are certains errors
    RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR

    // Complete normal process if no errors encountered above
    PRINT 'IMPORT SUCCEEDED'
END TRY
BEGIN CATCH
    PRINT 'IMPORT ABORTED. ERRORS ENCOUNTERED'
END CATCH

However, this is encountering an error and then continuing with the rest of the script. What am I missing? Thanks!

Dialyse

@ FailBoy - The issue that you have asked about is really T-SQL specific. If you could change your answer title to "TRY and RAISERROR in T-SQL" this would be clearer to the community. Thanks! – Disqualification

I will keep that in mind for next time, I see someone, I think you, has already changed the tag to tsql :) – Dialyse

@ FailBoy - I meant changing the title. I can't change the title, as I do not have edit priveleges. – Disqualification

oh right, I follow now, I have changed the title – Dialyse

Answer 1

It's because the severity of the RAISERROR is not high enough, needs to be between 11 and 19, as described here

e.g.

RAISERROR ('Errors found, please fix these errors and retry', 16, 2) WITH SETERROR

Liberate

Answer 2

I think you need to raise an error with a severity level higher than 10 for it to be caught, e.g.

RAISERROR ('Errors found', 11, 2) WITH SETERROR

Jon

Thanks for the answer! I have marked AdaTheDev's answer as correct because he included a link for other people to be able to follow. But thanks still. UpVote +1 – Dialyse

Answer 3

From MSDN

---

severity

Is the user-defined severity level associated with this message. Severity levels from 0 through 18 can be used by any user. Severity levels from 19 through 25 are used only by members of the sysadmin fixed server role. For severity levels from 19 through 25, the WITH LOG option is required.

Caution Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error log and the application log.

---

Try this instead:

RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR
RETURN

Kilpatrick