SQL Server custom exception RAISERROR using the example _mssql

When you use SQL Server stored procedures or triggers, you typically use custom exceptions to handle some special logic. For example, the cursor is destroyed and the transaction is rolled back. The use of SQL Server custom exceptions will be described in detail next.

Use "RAISERROR" to throw a custom exception. The following code: In a stored procedure, throw a custom exception, and then catch the custom exception in the Catch block.

IF EXISTS (SELECT * from sysobjects WHERE name= ' my_sp_test ' and type= ' P ') BEGIN
  DROP PROCEDURE my_sp_test;
End;
Go
CREATE PROCEDURE my_sp_test @i int, @outstr varchar (m) out as
begin try
  declare int;
  If @i<10 begin
   Set @outstr = ' system exception. ';
   Set @j = 10/0; -Because the divisor is 0, this will throw a system exception end
  else begin
   Set @j = @i;
   Set @outstr = ' Customer exception ';
   --Throws a custom exception to uniformly handle the exception
   RAISERROR (66666,--Message ID) in the last catch block.
      --Severity, 1--state
      ,
      );  
  End;
The end Try
begin catch 
  if @ @ERROR =66666 Begin--Determines whether the custom exception
    Set @outstr = @outstr + ' By the value of the @ @ERROR----------- -----customer exception ';
  End;
  return;
End catch;
Go
 

As above code, RAISERROR parameter description:

(1). Message ID: Unique identification of the exception, and this value is assigned to the SQL Server's system variable @ @Error. The message ID for a custom exception is recommended after 50000 because the system is not occupied by an exception within 50000.
(2). Severity: The level of the exception. You can enter a value of 1-19. 1-10 will not be caught by catch. 19 is a very serious level after that.
(3). State: If you enter a value that is negative or greater than 255, an error is generated, and an error causes the database to be disconnected

Execute the stored procedure to see if the custom exception was successfully captured:

DECLARE @OUTSTR11 VARCHAR (m);
exec dbo.my_sp_test, @OUTSTR11 out
print @OUTSTR11;