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

Question

I am trying 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 exceeding 5 characters results in the following error.

Msg 8152, Level 16, State 14, Line 1 String or binary data will be ignored. Application completed.

Is there a way to catch this error in a stored procedure? I tried putting if @@ERROR <> 0immediately after the insert statement, but the procedure never gets into error checking because the statement was aborted!

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

LEN, , . , , :

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