Question

Explain how to handle null values and default values when coding INSERT and UPDATE statements. Give examples.

Answer 1

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.

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.

The pattern I generally use is to create the row without the columns that have default constraints, then update the columns to replace the default values with supplied values (if not null).

Assuming col1 is the primary key and col4 and col5 have a default contraint

-- create initial row with default values
insert table1 (col1, col2, col3)
    values (@col1, @col2, @col3)

-- update default values, if supplied
update table1
    set col4 = isnull(@col4, col4),
        col5 = isnull(@col5, col5)
    where col1 = @col1

If you want the actual values defaulted into the table ...

-- create initial row with default values
insert table1 (col1, col2, col3)
    values (@col1, @col2, @col3)

-- create a container to hold the values actually inserted into the table
declare @inserted table (col4 datetime, col5 varchar(50))

-- update default values, if supplied
update table1
    set col4 = isnull(@col4, col4),
        col5 = isnull(@col5, col5)
    output inserted.col4, inserted.col5 into @inserted (col4, col5)
    where col1 = @col1

-- get the values defaulted into the table (optional)
select @col4 = col4, @col5 = col5 from @inserted