sql-server - SQL 服务器 : How to automatically adjust an out-of-range value for a column when insert/update?

Question

我在 Windows 7 上使用 MS-SQL Server 2014。
在数据库中，我目前有一个名为“STATUS”的表，其中一列的定义如下:

DeviceSerial   smallint

这个表中有/将有超过6k条记录。
不幸的是，有些设备使用错误的序列号进行编程，例如 43776 而不是 4376(技术人员输入了两次 7...)。通常，DeviceSerial 值应在以下范围内:1 - 9999。
显然，43776 是 smallint 的超出范围的值，因此插入/更新操作崩溃:(

好吧，问题是:在这种情况下，有没有办法让 sql-server 检查插入/更新的值，如果 DeviceSerial 值大于 9999，则将其置为 0？ (0 表示未设置序列号或其他内容)。

Answer 1

如果您有 SQL Server 2012+，则可以使用 TRYPARSE 或 TRYCONVERT。

如果 TRYPARSE 无法将输入的值转换为请求的数据类型，它将返回 NULL，可以合并为零。

因此，如果您尝试将 @MyVariable 插入到一个 smallint 中，您可以将其放入您的插入语句中:

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

插入不会出错，如果无法将值插入 smallint 列，则会插入一个零。

如果您不仅要强制一个值在 smallint 范围内，而且要在特定范围 1-9999 内，您可以使用 CASE 表达式来实现:

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