当参数为空时插入默认值

Question

我有一个表格，其中一列有一个默认值：

create table t (
    value varchar(50) default ('something')
)

我正在使用存储过程来插入值到这个表中：

create procedure t_insert (
    @value varchar(50) = null
)
as 
insert into t (value) values (@value)

问题是，当@value为null时，如何让它使用默认值？我尝试过：

insert into t (value) values ( isnull(@value, default) )

显然那样做不起作用。我还尝试了一个case语句，但效果也不好。还有其他建议吗？我这样做错了吗？

更新：我正在尝试完成这个操作而不必：

1. 在多个地方维护default值，并且
2. 使用多个insert语句。

如果不可能，那么我想我只能接受它。只是似乎应该可以实现这样的操作。

注意：我的实际表格有多列。我只是快速编写了一个示例。

- chrisofspades

---

只是好奇，因为你提出了一个很好的问题，但为什么不能更改表定义为非空？ - Brian Hasden

我喜欢ISNULL(@value, DEFAULT)语法 - 希望它能用。 - n8wrl

Answer 1

Christophe,

如果您在 INSERT 语句中没有指定列名，则默认值只会应用到该列。

由于您在插入语句中明确列出了列名，并将其明确设置为 NULL，这将覆盖该列的默认值。

您需要做的是，“如果将空值传递给您的 sproc，则不要尝试插入该列”。

以下是使用一些动态 SQL 快速解决此问题的简单示例。

创建一个带有一些默认值列的表...

CREATE TABLE myTable (
    always VARCHAR(50),
    value1 VARCHAR(50) DEFAULT ('defaultcol1'),
    value2 VARCHAR(50) DEFAULT ('defaultcol2'),
    value3 VARCHAR(50) DEFAULT ('defaultcol3')
)

创建一个动态构建并执行插入语句的存储过程，该插入语句基于输入参数。

ALTER PROCEDURE t_insert (
    @always VARCHAR(50),
    @value1 VARCHAR(50) = NULL,
    @value2 VARCHAR(50) = NULL,
    @value3 VARCAHR(50) = NULL
)
AS 
BEGIN
DECLARE @insertpart VARCHAR(500)
DECLARE @valuepart VARCHAR(500)

SET @insertpart = 'INSERT INTO myTable ('
SET @valuepart = 'VALUES ('

    IF @value1 IS NOT NULL
    BEGIN
        SET @insertpart = @insertpart + 'value1,'
        SET @valuepart = @valuepart + '''' + @value1 + ''', '
    END

    IF @value2 IS NOT NULL
    BEGIN
        SET @insertpart = @insertpart + 'value2,'
        SET @valuepart = @valuepart + '''' + @value2 + ''', '
    END

    IF @value3 IS NOT NULL
    BEGIN
        SET @insertpart = @insertpart + 'value3,'
        SET @valuepart = @valuepart + '''' + @value3 + ''', '
    END

    SET @insertpart = @insertpart + 'always) '
    SET @valuepart = @valuepart + + '''' + @always + ''')'

--print @insertpart + @valuepart
EXEC (@insertpart + @valuepart)
END

以下两个命令应该可以为您提供想要的输出示例...

EXEC t_insert 'alwaysvalue'
SELECT * FROM  myTable

EXEC t_insert 'alwaysvalue', 'val1'
SELECT * FROM  myTable

EXEC t_insert 'alwaysvalue', 'val1', 'val2', 'val3'
SELECT * FROM  myTable

我知道这是一种非常复杂的方式来完成您需要做的事情。您可能可以从InformationSchema中为相关列选择默认值，但说实话，我可能会考虑在过程顶部将默认值添加到参数中。

- Eoin Campbell

---

你能否提供一个例子：“您可以从InformationSchema中为相关列同样选择默认值”？ - binki

Answer 2

尝试使用if语句...

if @value is null 
    insert into t (value) values (default)
else
    insert into t (value) values (@value)

- Dave DuPlantis

---

我点赞太快了！唉！好吧，我不会给你个踩但是这并不完全是正确的答案！当没有为属性提供值时，将使用默认值。 - Richard T

使用DEFAULT关键字表示您希望数据库使用该字段的默认值。请参阅http://msdn.microsoft.com/en-us/library/ms174335(SQL.90).aspx。当我测试时，如果未向过程提供参数，则会插入列的默认值。 - Dave DuPlantis

这可能是最好的方法，不幸的是，您不能结合COALESCE和DEFAULT来删除IF。 - cfeduke

当您有一系列可能需要回退到默认值的参数时会发生什么？ - CJM

OP要求“无需使用多个插入语句”。 - binki

Answer 3

据我所知，在插入语句中没有指定值时，才会插入默认值。因此，在具有三个字段的表中（其中value2被默认设置），您需要执行以下操作：

INSERT INTO t (value1, value3) VALUES ('value1', 'value3')

然后value2将被默认。也许有人会介绍如何为单个字段的表完成这个任务。

- Brian Hasden

Answer 4

也许不是最高效的方法，但你可以创建一个标量函数，从信息模式中提取表和列名，然后使用之前尝试过的isnull逻辑来调用它：

    CREATE FUNCTION GetDefaultValue
    (
        @TableName VARCHAR(200),
        @ColumnName VARCHAR(200)
    )
    RETURNS VARCHAR(200)
    AS
    BEGIN
        -- you'd probably want to have different functions for different data types if
        -- you go this route
    RETURN (SELECT TOP 1 REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '(', ''), ')', ''), '''', '') 
            FROM information_schema.columns
            WHERE table_name = @TableName AND column_name = @ColumnName)

    END
    GO

然后像这样调用：

INSERT INTO t (value) VALUES ( ISNULL(@value, SELECT dbo.GetDefaultValue('t', 'value') )

- Lurker Indeed

Answer 5

到目前为止，最好的选择是为您的表创建一个INSTEAD OF INSERT触发器，从您的表中删除默认值，并将它们移动到触发器中。

具体操作如下：

create trigger dbo.OnInsertIntoT
ON TablenameT
INSTEAD OF INSERT
AS
insert into TablenameT
select
   IsNull(column1 ,<default_value>)
  ,IsNull(column2 ,<default_value>)
  ...
from inserted

无论什么代码试图向您的表中插入NULL值，这都能使其正常运行，避免了存储过程，完全透明，而且您只需要在一个地方维护默认值，即在此触发器中。请注意保留HTML标签。

- Blade

Answer 6

这是我能想到的最好方法。它可以防止SQL注入，只使用一个插入语句，并且可以通过添加更多的case语句来扩展。

CREATE PROCEDURE t_insert (     @value varchar(50) = null )
as
DECLARE @sQuery NVARCHAR (MAX);
SET @sQuery = N'
insert into __t (value) values ( '+
CASE WHEN @value IS NULL THEN ' default ' ELSE ' @value ' END +' );';

EXEC sp_executesql 
@stmt = @sQuery, 
@params = N'@value varchar(50)',
@value = @value;

GO

- sqlserverguy

Answer 7

chrisofspades，

据我所知，这种行为与数据库引擎的工作方式不兼容，但有一个简单（我不知道是否优雅，但是性能良好）的解决方案来实现您的两个目标：不要

1. 在多个位置维护默认值，并且
2. 使用多个插入语句。

解决方案是使用两个字段，一个可为空用于插入，另一个计算用于选择：

CREATE TABLE t (
    insValue VARCHAR(50) NULL
    , selValue AS ISNULL(insValue, 'something')
)

DECLARE @d VARCHAR(10)
INSERT INTO t (insValue) VALUES (@d) -- null
SELECT selValue FROM t

这种方法甚至可以让您将业务默认值集中管理在参数表中，放置一个特定的函数来实现此操作，例如更改：

selValue AS ISNULL(insValue, 'something')

对于

selValue AS ISNULL(insValue, **getDef(t,1)**)

我希望这能有所帮助。

- Nicolás Orlando

Answer 8

您可以使用存储过程参数的默认值：

CREATE PROCEDURE MyTestProcedure ( @MyParam1 INT,
@MyParam2 VARCHAR(20) = ‘ABC’,
@MyParam3 INT = NULL)
AS
BEGIN
    -- Procedure body here

END

如果没有提供@MyParam2，它将具有“ABC”的值...

- CMS

---

需要在至少两个地方保留默认值。 - Tom H

准确地说，汤姆，我希望避免这样做。 - chrisofspades

Answer 9

希望能够帮助像我一样的新手，使用MSSQL中的Upsert语句。 （这段代码我在我的MSSQL 2008 R2项目中使用，非常完美地工作。也许不是最佳实践...执行时间统计显示插入语句的执行时间为15毫秒）

只需将列的“默认值或绑定”字段设置为您决定用作列默认值的内容，并从设计菜单中将该列设置为不接受空值，然后创建此存储过程即可。

`USE [YourTable]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[YourTableName]

    @Value smallint,
    @Value1 bigint,
    @Value2 varchar(50),
    @Value3 varchar(20),
    @Value4 varchar(20),
    @Value5 date,
    @Value6 varchar(50),
    @Value7 tinyint,
    @Value8 tinyint,
    @Value9 varchar(20),
    @Value10 varchar(20),
    @Value11 varchar(250),
    @Value12 tinyint,
    @Value13 varbinary(max) 

在我的项目中，@Value13是一个存储为字节数组的照片列。 当没有传递照片到存储在数据库中的sp时，我计划使用默认照片。

AS
--SET NOCOUNT ON
IF @Value = 0 BEGIN
    INSERT INTO YourTableName (
        [TableColumn1],
        [TableColumn2],
        [TableColumn3],
        [TableColumn4],
        [TableColumn5],
        [TableColumn6],
        [TableColumn7],
        [TableColumn8],
        [TableColumn9],
        [TableColumn10],
        [TableColumn11],
        [TableColumn12],
        [TableColumn13]
    )
    VALUES (
        @Value1,
        @Value2,
        @Value3,
        @Value4,
        @Value5,
        @Value6,
        @Value7,
        @Value8,
        @Value9,
        @Value10,
        @Value11,
        @Value12,
        default
    )
    SELECT SCOPE_IDENTITY() As InsertedID
END
ELSE BEGIN
    UPDATE YourTableName SET 
        [TableColumn1] = @Value1,
        [TableColumn2] = @Value2,
        [TableColumn3] = @Value3,
        [TableColumn4] = @Value4,
        [TableColumn5] = @Value5,
        [TableColumn6] = @Value6,
        [TableColumn7] = @Value7,
        [TableColumn8] = @Value8,
        [TableColumn9] = @Value9,
        [TableColumn10] = @Value10,
        [TableColumn11] = @Value11,
        [TableColumn12] = @Value12,
        [TableColumn13] = @Value13
    WHERE [TableColumn] = @Value

END
GO`

- sihirbazzz

---

当您在回答中发布示例代码时，请尝试使示例代码足够小，以便轻松专注于哪个部分是答案。此外，您正在回答一个超过3年的问题，并且已经有一个被接受的答案。 - Marc Talbot

@MarcTalbot：谢谢你，你说得很对。正如你所看到的，我不是这些主题的专家。对于我的错误，我感到抱歉。在将来，我会注意这些规则的。再次感谢。问候； - sihirbazzz

Answer 10

如果表格中有足够的默认值，您可以简单地说：

INSERT t DEFAULT VALUES

请注意，这是一个相当不太可能的情况。

我只在生产环境中使用过一次。我们有两个密切相关的表格，并且需要保证两个表格都没有相同的UniqueID，因此我们有一个单独的表格，其中只有一个标识列，而插入到它的最佳方法是使用上述语法。

- Jonathan