RAISERROR and returning error message to the calling application

Question

Hi

Assuming Asp.Net app calls procedure dbo.ApprovePost and @@ERROR contains a value greater than 0, then the following code will be executed:

CREATE PROCEDURE dbo.ApprovePost
      …
IF @@ERROR > 0
 BEGIN
     RAISERROR(‘Approval of post failed’, 16, 1)
     ROLLBACK TRANSACTION ApprovePost
     RETURN 99
 END

COMMIT TRANSACTION ApprovePost

a) I don’t know much about stored procedures, but I thought that when RAISERROR raises an error, then no code after RAISERROR will be executed, since procedure will exit the moment error was raised?!

b) I assume RAISERROR function will return the error message back to calling Asp.Net application?

c) If so, then why does procedure also need to return value 99 to indicate failure ( BTW – I assume this value is returned to the calling Asp.Net application )?

d) And why value 99? Why not 100 or any other value?

thanx

Answer 1

Calling RAISERROR with a severity level higher than 10, it is 16 in your case, causes a SqlException in ADO.NET (severity levels 10 or lower are informational messages only), so returning 99 is pointless unless the exception is caught and you still can access the return parameter on the SqlCommand object (not sure it is accessible - haven't tested it).

Why 99? It's clearly some 'magic' value for the calling code.