How can I catch a truncation error in SQL Server 2005?

Question

I'm attempting to insert data into a table. Say for example that this is my table:

CREATE TABLE firstTable (first_name VARCHAR(5), last_name VARCHAR(10))

When I try to insert into this table, any data in the first_name field that exceeds 5 characters causes the following error.

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

Is there any way for me to catch this error in a stored procedure? I tried putting an if @@ERROR <> 0 right after the insert statement, but the procedure never gets to the error check because the statement has been terminated!

Any ideas?

TIA!

Answer 1

You can use TRY ... CATCH

BEGIN TRY

INSERT INTO firstTable VALUES ('long string','foo')

END TRY
BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()
/*The error will not be propagated to the client. You need
 to use RAISERROR if you want this to happen*/

END CATCH

Answer 2

You can use the LEN function to determine if the strings are too long before you do the insert. For example, if you wanted to just insert the truncated string you could do:

IF(LEN(@firstName) > 5) SET @firstName = LEFT(@firstName, 5)
IF(LEN(@lastName) > 10) SET @lastName = LEFT(@lastName, 10)