Insert default value when parameter is null

Question

I have a table that has a column with a default value:

create table t (     value varchar(50) default ('something') ) 

I'm using a stored procedure to insert values into this table:

create procedure t_insert (     @value varchar(50) = null ) as  insert into t (value) values (@value) 

The question is, how do I get it to use the default when @value is null? I tried:

insert into t (value) values ( isnull(@value, default) ) 

That obviously didn't work. Also tried a case statement, but that didn't fair well either. Any other suggestions? Am I going about this the wrong way?

Update: I'm trying to accomplish this without having to:

1. maintain the default value in multiple places, and
2. use multiple insert statements.

If this isn't possible, well I guess I'll just have to live with it. It just seems that something this should be attainable.

Note: my actual table has more than one column. I was just quickly writing an example.

Answer 1

Christophe,

The default value on a column is only applied if you don't specify the column in the INSERT statement.

Since you're explicitiy listing the column in your insert statement, and explicity setting it to NULL, that's overriding the default value for that column

What you need to do is "if a null is passed into your sproc then don't attempt to insert for that column".

This is a quick and nasty example of how to do that with some dynamic sql.

Create a table with some columns with default values...

CREATE TABLE myTable (     always VARCHAR(50),     value1 VARCHAR(50) DEFAULT ('defaultcol1'),     value2 VARCHAR(50) DEFAULT ('defaultcol2'),     value3 VARCHAR(50) DEFAULT ('defaultcol3') ) 

Create a SPROC that dynamically builds and executes your insert statement based on input params

ALTER PROCEDURE t_insert (     @always VARCHAR(50),     @value1 VARCHAR(50) = NULL,     @value2 VARCHAR(50) = NULL,     @value3 VARCAHR(50) = NULL ) AS  BEGIN DECLARE @insertpart VARCHAR(500) DECLARE @valuepart VARCHAR(500)  SET @insertpart = 'INSERT INTO myTable (' SET @valuepart = 'VALUES ('      IF @value1 IS NOT NULL     BEGIN         SET @insertpart = @insertpart + 'value1,'         SET @valuepart = @valuepart + '''' + @value1 + ''', '     END      IF @value2 IS NOT NULL     BEGIN         SET @insertpart = @insertpart + 'value2,'         SET @valuepart = @valuepart + '''' + @value2 + ''', '     END      IF @value3 IS NOT NULL     BEGIN         SET @insertpart = @insertpart + 'value3,'         SET @valuepart = @valuepart + '''' + @value3 + ''', '     END      SET @insertpart = @insertpart + 'always) '     SET @valuepart = @valuepart + + '''' + @always + ''')'  --print @insertpart + @valuepart EXEC (@insertpart + @valuepart) END 

The following 2 commands should give you an example of what you want as your outputs...

EXEC t_insert 'alwaysvalue' SELECT * FROM  myTable  EXEC t_insert 'alwaysvalue', 'val1' SELECT * FROM  myTable  EXEC t_insert 'alwaysvalue', 'val1', 'val2', 'val3' SELECT * FROM  myTable 

I know this is a very convoluted way of doing what you need to do. You could probably equally select the default value from the InformationSchema for the relevant columns but to be honest, I might consider just adding the default value to param at the top of the procedure