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

score 27 · Accepted Answer · answered Dec 13 '12 at 20:10

27

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

answered Dec 13 '12 at 20:10

Wrikken

69,272
8
97
136

How is this possible in MSSQL server ? – user1844205 Aug 12 '13 at 10:51
1

@user1844205: beats me, never use MSSQL, search MSSQL's docs, or ask the question here on SO. – Wrikken Aug 12 '13 at 10:57

Insert default into not null column if value is null

1 Answers1

Linked

Related