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

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.

Answer 2

You can use default values for the parameters of stored procedures:

Create Procedure MyTestProcedure ( @MyParam1 int,
@MyParam2 varchar(20) = ‘ABC’,
@MyParam3 int = NULL)
As
Begin
    -- Procedure body here

End

If @MyParam2 is not supplied, it will have the 'ABC' value...

Answer 3

Don't specify the column or value when inserting and the DEFAULT constaint's value will be substituted for the missing value.

I don't know how this would work in a single column table. I mean: it would, but it wouldn't be very useful.

Answer 4

The easiest way to do this is to modify the table declaration to be

CREATE TABLE Demo
(
    MyColumn VARCHAR(10) NOT NULL DEFALT 'Me'
)

Now, in your stored procedure you can do something like.

CREATE PROCEDURE InsertDemo
    @MyColumn VARCHAR(10) = null
AS
INSERT INTO Demo (MyColumn) VALUES(@MyColumn)

However, this method ONLY works if you can't have a null, otherwise, your stored procedure would have to use a different form of insert to trigger a default.

Answer 5

Try an if statement ...

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

Answer 6

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 varchar(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

Answer 7

The questioner needs to learn the difference between an empty value provided and null.

Others have posted the right basic answer: A provided value, including a null, is something and therefore it's used. Default ONLY provides a value when none is provided. But the real problem here is lack of understanding of the value of null.

.

Answer 8

You can use the COALESCE function in MS SQL.

INSERT INTO t ( value ) VALUES( COALESCE(@value, 'something') )

Personally, I'm not crazy about this solution as it is a maintenance nightmare if you want to change the default value.

My preference would be Mitchel Sellers proposal, but that doesn't work in MS SQL. Can't speak to other SQL dbms.

Answer 9

Probably not the most performance friendly way, but you could create a scalar function that pulls from the information schema with the table and column name, and then call that using the isnull logic you tried earlier:

    CREATE FUNCTION GetDefaultValue
    (
        @TableName varchar(200),
        @ColumnName varchar(200)
    )
    RETURNS varchar(200)
    AS
    BEGIN
        -- you'd probably want to have different functions for different data types if
        -- you go this route
    RETURN (select TOP 1 REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '(', ''), ')', ''), '''', '') 
         from information_schema.columns
         where table_name = @TableName and column_name = @ColumnName)

    END
    GO

And then call it like this:

insert into t (value) values ( isnull(@value, select dbo.GetDefaultValue('t', 'value') )