将空字符串插入SQL Server的INT列 [英] Insert empty string into INT column for SQL Server

问题描述

一个SAMPLE表只有一个int类型的列ID，默认为空.

A SAMPLE table has only one column ID of type int, default null.

在Oracle中，当我这样做时:

In Oracle when I do:

  insert into SAMPLE (ID) values ('');

新记录将添加空白值.但是在SQL Server 2008中，当我运行相同的insert语句时，新记录的值为0.

the new record is added with blank value. But in SQL Server 2008, when I run the same insert statement, the new record has the value of 0.

是否有一种方法可以强制SQL Server 2008将空白字符串默认为NULL而不是0(对于列的数字类型)?

Is there a way to force SQL Server 2008 to default blank string to NULL instead of 0 (for numerical type of columns)?

推荐答案

假定您的INSERT语句是存储过程的一部分，该存储过程已在应用程序的许多地方重复使用(或者，也许是始终由同一个构造的批处理客户端代码的一部分)，并且插入的值是作为字符串参数传递的数字，则可以这样修改INSERT:

Assuming that your INSERT statement is part of a stored procedure re-used in many places of your application (or, perhaps, is a batch always constructed by the same part of the client code) and that the inserted value is a number passed as a string argument, you could modify the INSERT like this:

INSERT INTO SAMPLE (ID) VALUES (NULLIF(@argument, ''));

这篇关于将空字符串插入SQL Server的INT列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！