Insert empty string into INT column for SQL Server

Question

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

In Oracle when I do:

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

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.

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

Answer 1

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, ''));