SQL Server Portal

In my earlier articles, I wrote about THROW statement and its enhanced features that give us more control over the debugging.
I also implemented it with some clients and found it much more effective than raiserror. Given below are the guidelines that must be followed to avoid any error.

1. We usually do not use (;) semicolon terminator in SQL Server, however it is recommended to use.  But make sure to use it prior to any statement before THROW statement. Given below is the example.

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
PRINT 'ERROR';--Do not forget to put this semicolon
--prior to THROW Statement
THROW
END CATCH

If you fail to use the semicolon terminator prior to THROW statement, SQL Server will generate given below error message.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘THROW’

2. Sometimes we pass THROW statement without parameter, so make sure that THROW Statement is inside CATCH block as shown in the above example. If you fail to do it, SQL Server will generate given below error message.Msg 10704, Level 15, State 1, Line 9
   To rethrow an error, a THROW statement must be used inside a CATCH block. Insert the THROW statement inside a CATCH block, or add error parameters to the THROW statement.
3. We usually pass the customized message_id in the THROW statement, make sure you use message_id between 50000 and 2147483647. Given below is the example.

   EXEC sys.sp_addmessage @msgnum = 80000 ,@severity = 16
   ,@msgtext = N'This is a test message.'
   ,@lang = 'us_english';
   GO
   
   THROW 80000 , N'This is a test message', 1;
   

   if you fail to do it, SQL Server will generate given below error message.
   Msg 35100, Level 16, State 10, Line 9
   Error number 49999 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

4. THROW does not allow the substitution parameter, so if you need to use it, always use with the FORMATMESSAGE function. Given below is the example.

   EXEC sys.sp_addmessage
   @msgnum = 90000
   ,@severity = 16
   ,@msgtext = N'This is a %s test message'
   ,@lang = 'us_english';
   GO
   
   DECLARE @msg NVARCHAR(50) =
   FORMATMESSAGE(90000, N'raresql');
   
   THROW 90000, @msg, 1;
   

Cleaning :

USE master;
GO
EXEC sp_dropmessage 80000;
GO
EXEC sp_dropmessage 90000;
GO

Reference : MSDN

Read Full Post »