9

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?

Curtis
  • 101,612
  • 66
  • 270
  • 352

1 Answers1

18
IF @SubPageDirectory IS NULL 
BEGIN 
    RAISERROR('@SubPageDirectory cannot be NULL', 16, 1) --change to > 10
    RETURN --exit now
END
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    @DaveShaw: no, it carries on executing – gbn Jul 15 '10 at 14:11
  • 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. – Curtis Jul 15 '10 at 14:12
  • +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. – KM. Jul 15 '10 at 14:32
  • 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. – Curtis Jul 15 '10 at 14:49
  • Sorry,meant to add severity level 16 means "user defined". 10 is a warning which is usually ignored – gbn Jul 15 '10 at 15:02
  • @gbn and @KM: I can't seem to find docs that clarifies this, but from my testing it seems that if `raiseerror` is called in a SProc that if called from a: 1.1. .NET app, will generate a .NET Exception and therefore no way check the Return Code, 1.2. an SSMS Query Window or another SProc or Function, will have its Param values show up in the Messages Tab (if from SSMS) and have no programmatic T-SQL way to retrieve the `raiseerror` Parameter values but the Return Code can be checked programmatically. Correct? – Tom Oct 06 '17 at 00:00
  • @Tom. To be honest, I would not rely on return codes. And I don't use them at all. If needed, use the state (last parameter of RAISERROR) or add a code to the string. Both can be parsed in the -net exception handler – gbn Oct 06 '17 at 07:27
  • @gbn: AFAIK, one doesn't have the option of checking Return Code in .NET if `raiseerror` had been called. I think Return Codes are useful in that they allow for definitive logic without relying on / restricting error messages to be / remain in a specific format. What about when Sprocs with `raiseerror` calls are called from T-SQL. Do you know of a way, via T-SQL, to query the Parameter Values of the `raiseerror` call from within T-SQL? – Tom Oct 06 '17 at 16:32
  • @Tom `DECLARE @rtn int; EXEC @rtn = MyStoredProc @p1..` Also, the *1* in the RAISERROR is *state* which can transfer information. Also see http://www.sommarskog.se/error-handling-I.html#returnvalue – gbn Oct 09 '17 at 06:31
  • @gbn: 1. Thx for the Link! However, I feel like The Matrix has been revealed to me. ;) I knew SS error handling was kooky, but not near this much!?! Ignorance is bliss. 2. That still doesn't answer my Q. of how to, "via T-SQL, to query the Parameter Values of the raiseerror call from within T-SQL". 3. Understandably (since it was last updated 11/29/09), it says "Unfortunately, you cannot reraise the exact error message, since RAISERROR does not permit you to use error numbers less than 50000." Since SS 2012, you can call "Thow" (w/ no parameters) to "reraise the exact error message". – Tom Oct 09 '17 at 17:23
  • @Tom: I use THROW now almost exlusively – gbn Oct 10 '17 at 08:20
  • @gbn: I would also, but I didn't write `raiseerror` Calls and they're on SS's < 2012. – Tom Oct 11 '17 at 15:55