Insert default value when parameter is null

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 BEGINDECLARE @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 + @valuepartEXEC (@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  myTableEXEC t_insert 'alwaysvalue', 'val1'SELECT * FROM  myTableEXEC 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

Try an if statement ...

if @value is null     insert into t (value) values (default)else    insert into t (value) values (@value)

As far as I know, the default value is only inserted when you don't specify a value in the insert statement. So, for example, you'd need to do something like the following in a table with three fields (value2 being defaulted)

INSERT INTO t (value1, value3) VALUES ('value1', 'value3')

And then value2 would be defaulted. Maybe someone will chime in on how to accomplish this for a table with a single field.