Question

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

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

Homework Answers

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
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Explain how coding and regulatory mutations each contribute to adaptation. Give an example of each.
Explain how coding and regulatory mutations each contribute to adaptation. Give an example of each.
Explain the following statement: “When re-coding variables or creating histograms, a practical question is how wide...
Explain the following statement: “When re-coding variables or creating histograms, a practical question is how wide each category should be. To avoid perceptions of lying with statistics, a rule of thumb is that categories should be based on category ranges of equal length.”
Essay: Give examples of Mission statements of three well-recognized companies. How can these mission statements affect...
Essay: Give examples of Mission statements of three well-recognized companies. How can these mission statements affect business analyst activity?
1) Explain how government use of subsidies can be counterproductive, give 3 examples. 2) Explain how...
1) Explain how government use of subsidies can be counterproductive, give 3 examples. 2) Explain how government use of taxes to decrease consumption can be counterproductive. Give 3 examples.
How do nurses’ own cultural values and beliefs affect their nursing care? Give specific examples of...
How do nurses’ own cultural values and beliefs affect their nursing care? Give specific examples of attitudes and their potential consequences
how is the medical device ekg applied to physics give examples explain
how is the medical device ekg applied to physics give examples explain
What is a leakage? When do we use a leakage? Please explain and give some examples,...
What is a leakage? When do we use a leakage? Please explain and give some examples, thank you.
GI, Nervous and GU system discussion 1. How do you code for ERCPs? explain in detail,...
GI, Nervous and GU system discussion 1. How do you code for ERCPs? explain in detail, give an example. 2. When do you use a mesh when coding a hernia repair? Explain and give an example 3. Explain the endoscopic procedures that can be performed in the GU system.
List six value attributes - on kinds of values customers can expect and experience when traveling....
List six value attributes - on kinds of values customers can expect and experience when traveling. 5 examples in how could the perceived value and delivered value be different in case of tourism? Give 4 examples on how companies operating in tourism industry can deliver on triple bottom line
How do you calculate sales tax? How do you calculate a discount? Explain and give examples
How do you calculate sales tax? How do you calculate a discount? Explain and give examples