Insert default into not null column if value is null

Question

I have a table foo, which has a NOT NULL column with a default called message:

CREATE TABLE foo(
    id int PRIMARY KEY,
    message varchar(64) NOT NULL DEFAULT 'Hello'
)

There is a stored procedure bar that inserts into foo:

CREATE PROCEDURE bar(
    i_id int,
    i_message varchar(64)
)
BEGIN

    -- other logic

    IF i_message IS NOT NULL THEN
        INSERT INTO foo (id, message) VALUES (i_id, i_message);
    ELSE
        INSERT INTO foo (id, message) VALUES (i_id, DEFAULT);
        -- could also be: INSERT INTO foo (id) VALUES (i_id);
    END IF;
 END;

You can see, I have to conditionally branch in order for my insert to use the default if i_message is null. That's fine for just one column, but consider if foo had many more NOT NULL DEFAULT columns - the syntax would be way to unwieldy.

Is there a syntax I can use to specify that an inserted value should fall back to the default if null? I tried the following but understandably got syntax errors:

INSERT INTO foo (id, message) VALUES (i_id, COALESCE(i_message, DEFAULT));
INSERT INTO foo (id, message) VALUES (i_id, IFNULL(i_message, DEFAULT));

Wrikken · Accepted Answer · 2012-12-13 20:10:26Z

27

  INSERT INTO foo (id, message) 
  VALUES
  (i_id, IFNULL(i_message,DEFAULT(message)));

answered Dec 13, 2012 at 20:10

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

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

2 Comments

user1844205 Over a year ago

How is this possible in MSSQL server ?

Wrikken Over a year ago

@user1844205: beats me, never use MSSQL, search MSSQL's docs, or ask the question here on SO.

Collectives™ on Stack Overflow

Insert default into not null column if value is null

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related