Monday, October 19, 2015

THROW vs RAISERROR in TRY/CATCH Block

During some of my practice tests, I encountered questions about error handling more than once. Here are some notes about the THROW and RAISERROR when used inside the TRY/CATCH Block:

  •  RAISERROR must have severity levels from 11-19 to transfer control to the CATCH block. (See: Detailed List of Severity levels). 
  •  RAISERROR and THROW in the TRY block will not cause SQL Server to send back an error message back to the client. 
  •  THROW with or without parameters in the CATCH block causes the batch to terminate so this needs to be the last statement to execute because any remaining commands following the THROW will not execute.
  • RAISERROR in the CATCH block can return error message back to the client but cannot re-raise the original error number. A custom error message number (50000) will need to be used.
  • RAISERROR statement does not terminate the batch.
  • THROW with parameteres always raises errors with custom error number and severity level 16.
  • THROW with parameters can also re-raise the original error message and send it back to the client.
Syntax of RAISERROR:

RAISERROR ({msg_id | msg_str | @local_variable }
{, severity ,state}
[, argument [,....n]])
[WITH option [,....n]]

Syntax of THROW:

THROW [ {error_number | @local_variable },
{message | @local_variable },
{state | @local_variable}
] [;]

No comments:

Post a Comment