SQLTeam.com | Weblogs | Forums

Question - Severity

What is the general rule of thumb when to use the following:


SET @Severity = 0
EXEC @Severity = TestSP 'xx3'


When you need to check on the return code from the proc.

A proc always returns an integer value as a return code, accessible by setting a variable to the EXEC result, as in your code. Typically 0 is used to mean a successful run, other values mean something else.

For example, TestSP might be written to return a -1 value to indicate that the calling code needs to wait and try again. Or a 99 value might mean that a specific error has occurred. And so on. Every proc can determine its own set of return values to use, there is no set of rules on that.

OK, thanks. So in this case if there is some type of issue it @Severity will return something other then a 0?

It could. But the TestSP code must explicitly set a different return code. By default, any proc will always return 0 (unless a SQL error causes it to exit).

So in the TestSP I would basically need to check for error and set the @severity level?

As an alternative / addition to the @severity return value from TestSP you can also use an OUPUT @Parameter (or more than one - e.g. @ErrorNo and @ErrorMessage)

SET @Severity = 0
EXEC @Severity = TestSP 'xx3', @ErrNo = @ErrNo OUTPUT