TSQL Enter default column value from Case Statement

Question

I would like to use the default value for the column in the insertion of the stored procedure, so I do not need to repeat the default value in several places (this can change ... the DRY principle).

The T-SQL INSERT operation has a convenient default keyword, which I can use as follows:

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 cannot force the case statement to return '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 can insert the default value and then update as needed like this:

 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 would really like someone to tell me: "There is a better way ..."

Here's the 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 

Answer 1

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

 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 insertion, so that you only insert the correct data into your table. It also keeps the number of INSERTS / UPDATES up to 1. You should also be careful when using @@ IDENTITY due to the review. Consider SCOPE_IDENTITY () .