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).

I would go for a constraint check instead of what you are think. A constraint check will avoid the insertion — Jorge Campos, Mar 02 '16 at 13:46
Thanks for the tip, but I would really need the insert to be done: it would be a nice feedback for me to see that all devices are properly set, etc. You see: 0 would mean that the device is not ok (not available), etc. — סטנלי גרונן, Mar 02 '16 at 13:54
Correct it in the app, **before** the insert. Let the database enforce the constraints. — Remus Rusanu, Mar 02 '16 at 14:07
@IvanStarostin : Not 1. But 2 and 3 if you can please elaborate (as regular answer). I am sql very beginner... — סטנלי גרונן, Mar 02 '16 at 14:18
@groenhen just posted a fully specified comprehensive solution for a very beginner which also does not loose wrong data so will let you fix device numbers later. As you can understand storing _zero_ will make it impossible to identify later who was it (which device). — Ivan Starostin, Mar 02 '16 at 14:39
@IvanStarostin : Not quite true, there are also other fields/columns like `IP address`, etc. — סטנלי גרונן, Mar 02 '16 at 14:43
@groenhen I reckon deviceserial may be '000321' and this is not equal to '321' — Ivan Starostin, Mar 02 '16 at 14:51

score 2 · Answer 1 · edited Mar 02 '16 at 14:01

2

You can use a Contraint in your table, that way if someone try to update or insert a value out of the range you get an error. It would mantain your database with the rigth data, and won't have a problem with devices without serials later.

ALTER TABLE Table
ADD CONSTRAINT CK_Table_Column_Range CHECK (
   DeviceSerial   >= 1 AND DeviceSerial   <= 9999--Inclusive
)

or

ALTER TABLE Table
ADD CONSTRAINT CK_Table_Column_Range CHECK (
  DeviceSerial  BETWEEN 1 AND 9999  
)

edited Mar 02 '16 at 14:01

סטנלי גרונן

2,917
23
46
68

answered Mar 02 '16 at 13:45

Luis Tellez

2,785
1
20
28

And I think its a better idea to not insert incongruent data in a database, and thats what I'm suggesting. – Luis Tellez Mar 02 '16 at 13:50
Thanks for the idea. Yet, it would be better to have some sort of auto-correct... I know it is not possible to have a real auto-correct, but at least I would like to have a 0 (zero) instead :) For the reason(s) explained above. – סטנלי גרונן Mar 02 '16 at 13:59
@groenhen, although you could "auto-correct" bad data using a trigger, I think it would be better to validate and reject bad data instead of silently fixing it. – Dan Guzman Mar 02 '16 at 14:01
@DanGuzman: Nice thing with the trigger. Even better if you can add an explanation as regular answer :) I would surely be interested and appreciate it :) Thx in advance! – סטנלי גרונן Mar 02 '16 at 14:08

Tab Alleman · Accepted Answer · 2016-03-02T14:22:49.757

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

Ivan Starostin · Answer 3 · 2016-03-02T14:44:56.130

0

There you go with a super-solution:

ALTER TABLE dbo.Status ALTER COLUMN DeviceSerial int NOT NULL

does not throw exception, lets you fix numbers any time you want, and you don't have to program any pseudo-views with instead-of triggers or stored proc with rewriting calls from client-side.

And this is a rare case where I'd suggest to use strings instead of numeric values. Serial number usually means a unique set of characters which may be digits and letters. And often it may have leading zero which means nothing for numeric values but is a regular and persistent part of a unique string.

edited Mar 02 '16 at 14:44

answered Mar 02 '16 at 14:33

Ivan Starostin

8,798
5
21
39

Hehe, that's the "fix" what I have right now (`int` type)... :) – סטנלי גרונן Mar 02 '16 at 14:44
I believe this is the right one (regardless reasoning about strings). – Ivan Starostin Mar 02 '16 at 14:48

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

3 Answers3