SQL Server custom exception raiserror example, serverraiserror

SQL Server custom exception raiserror example, serverraiserror

When using SQL Server Stored Procedures or triggers, custom exceptions are usually used to handle some special logic. For example, the cursor is destroyed and the transaction is rolled back. Next, we will introduce in detail the use of SQL Server custom exceptions.

Use "raiserror" to throw a custom exception. The following code throws a custom exception during the stored procedure and captures 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; GOcreate procedure my_sp_test @ I int, @ outstr varchar (100) out asbegin try declare @ j int; if @ I <10 begin set @ outstr = 'System exception. '; set @ j = 10/0; -- because the divisor is 0, an exception of end else begin set @ j = @ I will be thrown here; set @ outstr = 'customer exception'; -- throws a custom exception and processes the exception RAISERROR (66666, -- Message id in the final catch Block. 16, -- Severity, 1 -- State,); end; end trybegin catch if @ ERROR = 66666 begin -- determine whether a custom exception is set @ outstr = @ outstr + '-------------- customer exception' through the value of @ ERROR; end; return; end catch; go

The preceding Code describes the raiserror parameters:

(1). Message id: the unique identifier of the exception. This value is assigned to the SQLServer system variable @ Error. Message IDs with custom exceptions are recommended to be later than 50000, because messages with a value less than 50000 will be occupied by system exceptions.
(2). Severity: exception level. You can enter a value ranging from 1 to 19. Between 1 and 10 won't be caught by catch. 19 and later are very serious levels.
(3). State: If the input value is negative or greater than 255, an error is generated. If an error occurs, the database connection will be interrupted.

Run the stored procedure to check whether custom exceptions are successfully captured:

DECLARE @OUTSTR11 VARCHAR(100);exec dbo.my_sp_test 12,@OUTSTR11 outprint @OUTSTR11;