参数为空时插入默认值 [英] Insert default value when parameter is null

问题描述

我有一个表，其中有一列具有默认值:

I have a table that has a column with a default value:

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

我正在使用存储过程将值插入到此表中:

I'm using a stored procedure to insert values into this table:

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

问题是，当 @value 为 null 时，如何让它使用默认值?我试过了:

The question is, how do I get it to use the default when @value is null? I tried:

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

这显然不起作用.还尝试了 case 语句，但这也不公平.还有其他建议吗?我是不是用错了方法?

That obviously didn't work. Also tried a case statement, but that didn't fair well either. Any other suggestions? Am I going about this the wrong way?

更新:我正在尝试无需完成此操作:

Update: I'm trying to accomplish this without having to:

1. 在多个地方维护 default 值，以及
2. 使用多个insert 语句.

1. maintain the default value in multiple places, and
2. use multiple insert statements.

如果这是不可能的，那么我想我只能忍受它了.似乎这应该是可以实现的.

If this isn't possible, well I guess I'll just have to live with it. It just seems that something this should be attainable.

注意:我的实际表格有不止一列.我只是在快速写一个例子.

Note: my actual table has more than one column. I was just quickly writing an example.

推荐答案

Christophe,

Christophe,

仅当您未在 INSERT 语句中指定列时，才会应用列的默认值.

The default value on a column is only applied if you don't specify the column in the INSERT statement.

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

Since you're explicitiy listing the column in your insert statement, and explicity setting it to NULL, that's overriding the default value for that column

您需要做的是如果将 null 传递到您的 sproc，则不要尝试为该列插入".

What you need to do is "if a null is passed into your sproc then don't attempt to insert for that column".

这是一个快速而讨厌的例子，说明如何使用一些动态 sql 来做到这一点.

This is a quick and nasty example of how to do that with some dynamic sql.

用默认值创建一个包含一些列的表...

Create a table with some columns with default values...

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

创建一个基于输入参数动态构建和执行插入语句的 SPROC

Create a SPROC that dynamically builds and executes your insert statement based on input params

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

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

The following 2 commands should give you an example of what you want as your outputs...

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 中为相关列选择默认值，但老实说，我可能会考虑将默认值添加到过程顶部的 param

I know this is a very convoluted way of doing what you need to do. You could probably equally select the default value from the InformationSchema for the relevant columns but to be honest, I might consider just adding the default value to param at the top of the procedure

这篇关于参数为空时插入默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！