SQL Server catch error from extended stored procedure

Question

Hello I have an extended stored procedure that sends an error message.

srv_sendmsg(pSrvProc, SRV_MSG_ERROR, errorNum, SRV_FATAL_SERVER, 1,
            NULL, 0, (DBUSMALLINT) __LINE__, 
            buff,
            SRV_NULLTERM);

I've set the severity to SVR_FATAL_SERVER just as a test to see if I can cause the message to throw an exception in the sql.

In my SQL i'm doing:

BEGIN TRY
    EXEC dbo.xp_somethingCool
    SET @Error = @@ERROR
END TRY
BEGIN CATCH
    PRINT 'AN Error occoured!'
    SELECT ERROR_NUMBER() AS ErrorNumber
          ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

I would think that when my xp sends the error message the tsql would catch the error and select the error_number and error_message. Instead what ends up happening is that the xp sends the message and the T-SQL continues on its way like nothing happened. The @@Error variable doesn't get set either.

So I was wondering if there was any trick to getting SQL to catch an error from an XP ?

Thanks, Raul

Answer 1

You should be able to use RAISERROR to throw an exception.

Per comment: Are you not creating your own extended stored proc? If so, you can use throw or do something like 1/0 to throw an exception.

Answer 2

Never raise anything so high. Anything raised with severity above 16 will abort the batch, so your T-SQL catch block never gets a chance to run. Something as high as SVR_FATAL_SERVER will shutdown the server immedeatly.

Answer 3

You can only test the result from an extended stored proc, and use that to throw an exception.

...
EXEC @rtn = dbo.xp_somethingCool
IF @rtn <> 0
    RAISERROR ...
...

In very simple terms, an extended stored proc is not SQL run by the database engine so you can't issue RAISERROR. See KB 190987 for some more info