SQL Server: How to automatically adjust an out-of-range value for a column when insert/update?

Question

I am using MS-SQL Server 2014 on Windows 7.
In the database, I currently have a table named "STATUS" where a column is defined like:

DeviceSerial   smallint

There are/will be more than 6k records in this table.
Unfortunately some devices were programmed with wrong serial numbers, for example 43776 instead of 4376 (the tech guy typed 7 twice...). Normally, the DeviceSerial value should be in range: 1 - 9999.
Obviously, 43776 is an out-of-range value for a smallint, hence the insert/update operation crashes :(

Well, the question is: In such situation(s), is there a way to do make the sql-server check the inserted/updated value and, if the DeviceSerial value is greater than 9999, put 0 for it? (0 would mean serial number not set or something).

Answer 1

If you have SQL Server 2012+ you can use TRYPARSE or TRYCONVERT.

If TRYPARSE cannot convert the entered value to the requested datatype, it returns NULL, which can be coalesced to zero.

So if you were trying to insert @MyVariable into a smallint, you could put this in your insert statement:

COALESCE(TRYPARSE(@MyVariable AS smallint),0)

The insert will not error, and a zero will be inserted if the value cannot be inserted into a smallint column.

If you want to enforce not only that a value is in the smallint range but is in the specific range 1-9999, you can do it with a CASE expression:

CASE WHEN @MyVariable BETWEEN 1 AND 9999 THEN @MyVariable ELSE 0 END