SQL SERVER – How to Catch Errors While Inserting Values in Table

Question: “I often get errors when I insert values into a table, I want to gracefully catch them, how do I do that.”

Answer: Very simple. Just use TRY… CATCH. Here is the simple example of TRY…CATCH I have blogged earlier when it was introduced.

Here is the example, I have build from the earlier blog post where user can catch the error details during inserting value in table.

First, we will create a sample table.

CREATE TABLE SampleTable (ID INT IDENTITY( 1,1), Col VARCHAR( 10))
GO


Now we will attempt to insert value in this table which will throw errors and the same error we will catch into the table.

BEGIN TRY
INSERT INTO SampleTable (Col)
SELECT 'FourthRow '
UNION ALL
SELECT 'FifthRow ---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER( ) AS ErrorNumber
, ERROR_MESSAGE( ) AS ErrorMessage;
END CATCH

GO


The second row of the above table will throw an error as the length of the row is larger than the column in which we are inserting values. It will throw an error and the same error will be caught via TRY…CATCH and it will be displayed in the SELECT statement. Here is the result set.

Reference: Pinal Dave (http://ift.tt/wkSzP3 )

Filed under: PostADay, SQL, SQL Authority, SQL Error Messages, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, T SQL



via Journey to SQL Authority with Pinal Dave http://ift.tt/1uLGYi3