SQL Server : set a default value for Insert Into query when parameter is null

Question

I'm trying to do an insert query like follow:

Insert Into table1 (column1, column2)
Values (#value1#, #value2#)

But let's say, I want to default column2 to 2/19/2013 when the parameter #value2# is null. How would I achieve that?

I'm using Ibatis framework, and the insert statement is in a XML file, which is used in a db file that is called from the controller.

you want query in sql, right? what is data type for column2? — DevelopmentIsMyPassion
– DevelopmentIsMyPassion, Commented Feb 19, 2013 at 20:44

Tom · Accepted Answer · 2013-02-19 20:48:50Z

2

This may be what you're looking for:

ISNULL(#value2#,'2/19/2013')

answered Feb 19, 2013 at 20:48

Tom

2,2407 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KM. Over a year ago

probably want to use GETDATE() in place of string literal: '2/19/2013', since today is 2/19/2013

peterm · Accepted Answer · 2013-02-19 21:30:37Z

2

If you can't change insert statements which are coming from an XML file, you might consider creating a trigger

CREATE TRIGGER table1_Insert ON table1
INSTEAD OF INSERT
AS
INSERT INTO table1 (column1, column2)
    SELECT column1, ISNULL(column2,'2/19/2013') FROM INSERTED;

Then if try to insert a NULL

INSERT INTO table1 VALUES (1, NULL);

You'll have

| COLUMN1 |   COLUMN2 |
-----------------------
|       1 | 2/19/2013 |

Here is sqlfiddle

edited Feb 19, 2013 at 21:30

answered Feb 19, 2013 at 21:09

peterm

93k16 gold badges157 silver badges162 bronze badges

2 Comments

KM. Over a year ago

probably want to use GETDATE() in place of string literal: '2/19/2013', since today is 2/19/2013

peterm Over a year ago

Absolutely if the intent is to put the current date and not some base date or something else.

Collectives™ on Stack Overflow

SQL Server : set a default value for Insert Into query when parameter is null

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related