Question - Severity

Transact-SQL
1

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

@Severity

Example
DECLARE @Severity INT
SET @Severity = 0
EXEC @Severity = TestSP 'xx3'

Thanks,
Mac

2

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.

3

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

4

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).

5

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

6

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)

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