Dealing with Errors when Inserting Data

You can receive error messages during insert operations. Moreover, they can be of different types.

Try to execute the following code:

 INSERT INTO [AdventureWorks].[Sales].[Currency]            ([CurrencyCode]            ,[Name]            ,[ModifiedDate])      VALUES            ('NEW'             ,'New currency') 

You will find that the following error appears:

 Msg 109, Level 15, State 1, Line 1 There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. 

This is obvious, for you are supplying fewer values than the specified columns. However, the message title provides various information besides the text itself. It appears that the messages are numbered (e.g., 109), and they contain levels (15) and states (1).

If you execute the following sentence , you can view the complete list of error messages defined in SQL Server 2005, which is stored in the sys.messages table.

 select * from sys.messages 

The table contains 7549 error messages in nine different languages.

The level value supplies information about the type of message. Some errors are not true errors, but are merely warnings. However, any of them will be raised to the application. It is sometimes necessary to know the exact level of the received message to decide whether it deserves to be managed in your application.

You can use the following table as a guideline.

Table 10-1: Severity Levels

Level	Meaning
	Succeed
1 to 9	Information only; no real error
10	Information or not severe error
11 to16	Errors that can be corrected by the user
17 to 19	Software errors to be reported to the system administrator
20 to 25	Severe error; execution will be canceled

When inserting data, you may receive the previous missing values error or other errors, as demonstrated by executing the following invalid SQL Server sentences.

You cannot insert a string value in a datetime column, as in the following code.

 INSERT INTO [AdventureWorks].[Sales].[Currency]            ([CurrencyCode]            ,[Name]            ,[ModifiedDate])      VALUES            ('NEW'            ,'New currency'            ,'Today') 

The following error is the result:

 Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string. 

The CurrencyCode column has a defined size that is less than the text you are trying to insert in the following example.

 INSERT INTO [AdventureWorks].[Sales].[Currency]            ([CurrencyCode]            ,[Name]            ,[ModifiedDate]) VALUES       ('NEW CURRENCY TO ADD IN THE CURRENCY TABLE'       ,'New currency'       ,getdate()) 

The following error is the result:

 Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated. The statement has been terminated. 

The CurrencyRate table has a foreign key restriction with the CurrencyCode table. The ˜NEW code is not found in the CurrencyRate table in the following code.

 INSERT INTO [AdventureWorks].[Sales].[CurrencyRate]            ([CurrencyRateDate]            ,[FromCurrencyCode]            ,[ToCurrencyCode]            ,[AverageRate]            ,[EndOfDayRate]            ,[ModifiedDate])      VALUES            (getdate()            ,'NEW'            ,'USD'            ,1            ,1            ,getdate()) 

The following error is the result:

 Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CurrencyRate_Currency_ FromCurrencyCode". The conflict occurred in database "AdventureWorks", table "Sales.Currency ", column 'CurrencyCode'. The statement has been terminated. 

Managing the Error

In SQL Server 2000 and previous versions, you can determine whether an error occurs using the @@ERROR system variable. This variable only provides the error number; you can check whether an error occurs by checking the variable immediately after executing a sentence.

   DECLARE @ErrVar INT INSERT INTO [AdventureWorks].[Sales].[CurrencyRate]            ([CurrencyRateDate]            ,[FromCurrencyCode]            ,[ToCurrencyCode]            ,[AverageRate]            ,[EndOfDayRate]            ,[ModifiedDate])      VALUES            (getdate()            ,'NEW'            ,'USD'            ,1            ,1            ,getdate()) SET @ErrVar=@@ERROR IF @ErrVar<>0    PRINT 'error: '+ cast(@ErrVar AS nvarchar(8))   

SQL Server 2005 helps you manage errors more efficiently by implementing the TRY CATCH structure, which is similar to that used in .NET languages. Using TRY , you can obtain more information about the error by using the following functions.

ERROR_NUMBER()

ERROR_SEVERITY()

ERROR_STATE()

ERROR_LINE()

ERROR_PROCEDURE()

ERROR_MESSAGE()

If an error occurs in the following example, the query returns a row with complete information about the error.

 BEGIN TRY INSERT INTO [AdventureWorks].[Sales].[CurrencyRate]            ([CurrencyRateDate]            ,[FromCurrencyCode]            ,[ToCurrencyCode]            ,[AverageRate]            ,[EndOfDayRate]            ,[ModifiedDate])      VALUES            (getdate()            ,'NEW'            ,'USD'            ,1            ,1            ,getdate()) END TRY BEGIN CATCH SELECT         ERROR_NUMBER() AS [Error Number],         ERROR_SEVERITY() AS Severity,         ERROR_STATE()AS State,         ERROR_LINE ()AS Line,         ERROR_PROCEDURE()AS [Procedure],         ERROR_MESSAGE()AS Message; END CATCH