SQL 参数为空时插入默认值

Insert default value when parameter is null

提问by chrisofspades

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)

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

问题是，我如何让它在@valueis时使用默认值null？我试过：

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

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

那显然行不通。也尝试了一个case声明，但这也不公平。还有其他建议吗？我会以错误的方式解决这个问题吗？

Update: I'm trying to accomplish this withouthaving to:

更新：我正在尝试无需执行以下操作即可完成此操作：

1. maintain the defaultvalue in multiple places, and
2. use multiple insertstatements.

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

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.

注意：我的实际表有不止一列。我只是快速写一个例子。

回答by Dave DuPlantis

Try an if statement ...

尝试一个 if 语句...

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

回答by Eoin Campbell

Christophe,

克里斯托夫，

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

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

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，因此覆盖了该列的默认值

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

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

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

这是一个快速而讨厌的示例，说明如何使用一些动态 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')
)

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

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

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

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

以下 2 个命令应该给你一个你想要作为输出的例子......

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

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

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

回答by Brian Hasden

As far as I know, the default value is only inserted when you don't specify a value in the insert statement. So, for example, you'd need to do something like the following in a table with three fields (value2 being defaulted)

据我所知，只有在插入语句中没有指定值时才会插入默认值。因此，例如，您需要在包含三个字段的表中执行以下操作（默认值为 value2）

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

And then value2 would be defaulted. Maybe someone will chime in on how to accomplish this for a table with a single field.

然后 value2 将被默认。也许有人会就如何为具有单个字段的表完成此操作提出意见。

回答by Lurker Indeed

Probably not the most performance friendly way, but you could create a scalar function that pulls from the information schema with the table and column name, and then call that using the isnull logic you tried earlier:

可能不是对性能最友好的方式，但您可以创建一个标量函数，该函数从具有表名和列名的信息模式中提取，然后使用您之前尝试过的 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

And then call it like this:

然后像这样调用它：

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

回答by Nicolás Orlando

chrisofspades,

黑桃，

As far as I know that behavior is not compatible with the way the db engine works, but there is a simple (i don't know if elegant, but performant) solution to achive your two objectives of DO NOT

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

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

1. 在多个地方保持默认值，以及
2. 使用多个插入语句。

The solution is to use two fields, one nullable for insert, and other one calculated to selections:

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

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

This method even let You centralize the management of business defaults in a parameter table, placing an ad hoc function to do this, vg changing:

这种方法甚至可以让您集中管理参数表中的业务默认值，放置一个专门的功能来做到这一点，vg 更改：

selValue AS ISNULL(insValue, 'something')

for

为了

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

I hope this helps.

我希望这有帮助。

回答by sqlserverguy

This is the best I can come up with. It prevents sql injection uses only one insert statement and can ge extended with more case statements.

这是我能想到的最好的方法。它可以防止 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

回答by CMS

You can use default values for the parameters of stored procedures:

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

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

END

If @MyParam2 is not supplied, it will have the 'ABC' value...

如果未提供 @MyParam2，它将具有 'ABC' 值...

回答by cfeduke

Don't specify the column or value when inserting and the DEFAULT constaint's value will be substituted for the missing value.

插入时不要指定列或值，默认值将替换缺失值。

I don't know how this would work in a single column table. I mean: it would, but it wouldn't be very useful.

我不知道这在单列表中如何工作。我的意思是：它会，但它不会很有用。

回答by sihirbazzz

Hope To help to -newbie as i am- Ones who uses Upsert statements in MSSQL.. (This code i used in my project on MSSQL 2008 R2 and works simply perfect..May be It's not Best Practise.. Execution time statistics shows execution time as 15 milliSeconds with insert statement)

希望对 - 像我一样的新手 - 在 MSSQL 中使用 Upsert 语句的人有所帮助。插入语句的时间为 15 毫秒）

Just set your column's "Default value or binding" field as what you decide to use as default value for your column and Also set the column as Not accept null values from design menu and create this stored Proc..

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

`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) 

-- in my project @Value13 is a photo column which storing as byte array.. --And i planned to use a default photo when there is no photo passed --to sp to store in db..

-- 在我的项目中，@Value13 是一个照片列，它存储为字节数组.. --我计划在没有照片传递时使用默认照片 --to sp 存储在 db 中..

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`

回答by Jonathan

With enough defaults on a table, you can simply say:

表上有足够的默认值，您可以简单地说：

INSERT t DEFAULT VALUES

Note that this is quite an unlikely case, however.

但是请注意，这是一种不太可能的情况。

I've only had to use it once in a production environment. We had two closely related tables, and needed to guarantee that neither table had the same UniqueID, so we had a separate table which just had an identity column, and the best way to insert into it was with the syntax above.

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