T-SQL - Return custom error message and end query

Question

I have a lengthy stored procedure in which I would like to do something like the following:

IF @SubPageDirectory IS NULL
BEGIN
    RAISERROR('@SubPageDirectory cannot be NULL', 10, 1)
    EXIT STORED PROCEDURE
END

Basically I wish to check whether my variable is NULL, and if it is, return an error message to my .NET Web Application, and stop running the query at that stage. How can I achieve this?

gbn · Accepted Answer · 2010-07-15 13:58:46Z

18

IF @SubPageDirectory IS NULL 
BEGIN 
    RAISERROR('@SubPageDirectory cannot be NULL', 16, 1) --change to > 10
    RETURN --exit now
END

answered Jul 15, 2010 at 13:58

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

gbn Over a year ago

@DaveShaw: no, it carries on executing

Curtis Over a year ago

RETURN seems to return without any errors, as in, it won't show the user the errors, it will just continue. RAISERROR on its own continues reading through the stored procedure as it will then bring up another error.

KM. Over a year ago

+1, however, I'd make it RETURN n where n is an integer. I make negative return values warning messages (invalid user input, etc) and positive return values fatal errors (insert failure, etc). The calling application can determine how to handle the message (hard stop and/or just display the message) based on the return value being positive/negative.

Curtis Over a year ago

I've increased the severity level to 16, and now my asp.net web app shows a server error page, which is what I was hoping for. Return has also prevented the query from continuing. I had previously overlooked the severity level increase comment.

gbn Over a year ago

Sorry,meant to add severity level 16 means "user defined". 10 is a warning which is usually ignored

|

Collectives™ on Stack Overflow

T-SQL - Return custom error message and end query

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related