TSQL从Case Statement插入列默认值

I'd like to use the column's default value in an stored procedure insert, so that I don't have to repeat the default value in multiple places (it could change... DRY principle).

我想在存储过程插入中使用列的默认值，这样我就不必在多个位置重复默认值（它可能会改变...... DRY原则）。

The T-SQL INSERT operation has a handy 'default' keyword that I can use as follows:

T-SQL INSERT操作有一个方便的'default'关键字，我可以使用如下：

Declare @newA varchar(10)
Set @newA = 'Foo2'

-- I can use "default" like so...
Insert into Table_1 (
    A, 
    B) 
Values (
    @newA, 
    default)

However, If I need to do something conditional, I can't seem to get the case statement to return 'default'.

但是，如果我需要做一些有条件的事情，我似乎无法让case语句返回'default'。

-- How do I use 'default' in a case statement? 
INSERT INTO Table_1 (
    A,
    B )
VALUES (
    @newA,
    CASE WHEN (@newA <> 'Foo2') THEN 'bar' ELSE default END)
-- > yeilds "Incorrect syntax near the keyword 'default'."

I could insert the default, and then update as needed like so:

我可以插入默认值，然后根据需要更新，如下所示：

INSERT INTO Table_1 (
    A,
    B )
VALUES (
    @newA,
    default)
UPDATE Table_1 
SET B = CASE WHEN (A <> 'Foo2') THEN 'bar' ELSE B END
WHERE ID = SCOPE_IDENTITY()

But I'd really like somebody to tell me "There's a better way..."

但我真的希望有人告诉我“有更好的方法......”

Here's a table definition for this example if it helps...

这是这个例子的表定义，如果它有帮助......

CREATE TABLE dbo.Table_1 (
    ID int NOT NULL IDENTITY (1, 1),
    A varchar(10) NULL,
    B varchar(10) NULL  )  
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT DF_Table_1_A DEFAULT 'A-Def' FOR A
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT DF_Table_1_B DEFAULT 'B-Def' FOR B
GO

1 个解决方案

#1

default only works from within a VALUES() block, which does not seem to be an acceptable value in a CASE statement; you could use an if statement to determine what to insert:

default仅在VALUES（）块中起作用，在CASE语句中似乎不是可接受的值;您可以使用if语句来确定要插入的内容：

DECLARE @newA varchar(10) = 'Foo2'

IF (@newA <> 'Foo2')
BEGIN
   INSERT INTO Table_1 (A, B)
   SELECT @newA, 'bar'
END
ELSE
BEGIN
   --If you are using default values, you do not have to specify the column
   INSERT INTO Table_1 (A) 
   SELECT @newA
END

I think this is better than updating after an insert, so that you only insert correct data into your table. It also keeps the number of INSERTS/UPDATES to 1. You should also be careful when you using @@IDENTITY due to scoping. Consider looking into SCOPE_IDENTITY().

我认为这比插入后更新更好，因此您只需将正确的数据插入表中。它还将INSERTS / UPDATES的数量保持为1.由于范围界定，使用@@ IDENTITY时也应该小心。考虑调查SCOPE_IDENTITY（）。