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

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.

The best option by far is to create an INSTEAD OF INSERT trigger for your table, removing the default values from your table, and moving them into the trigger.

This will look like the following:

create trigger dbo.OnInsertIntoT
ON TablenameT
INSTEAD OF INSERT
AS
insert into TablenameT
select
   IsNull(column1 ,<default_value>)
  ,IsNull(column2 ,<default_value>)
  ...
from inserted

This makes it work NO MATTER what code tries to insert NULLs into your table, avoids stored procedures, is completely transparent, and you only need to maintain your default values in one place, namely this trigger.

This is the best I can come up with. It prevents sql injection uses only one insert statement and can ge extended with more case statements.

CREATE PROCEDURE t_insert (     @value varchar(50) = null )
as
DECLARE @sQuery NVARCHAR (MAX);
SET @sQuery = N'
insert into __t (value) values ( '+
CASE WHEN @value IS NULL THEN ' default ' ELSE ' @value ' END +' );';

EXEC sp_executesql 
@stmt = @sQuery, 
@params = N'@value varchar(50)',
@value = @value;

GO

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') )

chrisofspades,

As far as I know that behavior is not compatible with the way the db engine works, but there is a simple (i don't know if elegant, but performant) solution to achive your two objectives of DO NOT

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

The solution is to use two fields, one nullable for insert, and other one calculated to selections:

CREATE TABLE t (
    insValue VARCHAR(50) NULL
    , selValue AS ISNULL(insValue, 'something')
)

DECLARE @d VARCHAR(10)
INSERT INTO t (insValue) VALUES (@d) -- null
SELECT selValue FROM t

This method even let You centralize the management of business defaults in a parameter table, placing an ad hoc function to do this, vg changing:

selValue AS ISNULL(insValue, 'something')

for

selValue AS ISNULL(insValue, **getDef(t,1)**)

I hope this helps.

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...

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.

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.

Hope To help to -newbie as i am- Ones who uses Upsert statements in MSSQL.. (This code i used in my project on MSSQL 2008 R2 and works simply perfect..May be It's not Best Practise.. Execution time statistics shows execution time as 15 milliSeconds with insert statement)

Just set your column's "Default value or binding" field as what you decide to use as default value for your column and Also set the column as Not accept null values from design menu and create this stored Proc..

`USE [YourTable]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[YourTableName]

    @Value smallint,
    @Value1 bigint,
    @Value2 varchar(50),
    @Value3 varchar(20),
    @Value4 varchar(20),
    @Value5 date,
    @Value6 varchar(50),
    @Value7 tinyint,
    @Value8 tinyint,
    @Value9 varchar(20),
    @Value10 varchar(20),
    @Value11 varchar(250),
    @Value12 tinyint,
    @Value13 varbinary(max) 

-- in my project @Value13 is a photo column which storing as byte array.. --And i planned to use a default photo when there is no photo passed --to sp to store in db..

AS
--SET NOCOUNT ON
IF @Value = 0 BEGIN
    INSERT INTO YourTableName (
        [TableColumn1],
        [TableColumn2],
        [TableColumn3],
        [TableColumn4],
        [TableColumn5],
        [TableColumn6],
        [TableColumn7],
        [TableColumn8],
        [TableColumn9],
        [TableColumn10],
        [TableColumn11],
        [TableColumn12],
        [TableColumn13]
    )
    VALUES (
        @Value1,
        @Value2,
        @Value3,
        @Value4,
        @Value5,
        @Value6,
        @Value7,
        @Value8,
        @Value9,
        @Value10,
        @Value11,
        @Value12,
        default
    )
    SELECT SCOPE_IDENTITY() As InsertedID
END
ELSE BEGIN
    UPDATE YourTableName SET 
        [TableColumn1] = @Value1,
        [TableColumn2] = @Value2,
        [TableColumn3] = @Value3,
        [TableColumn4] = @Value4,
        [TableColumn5] = @Value5,
        [TableColumn6] = @Value6,
        [TableColumn7] = @Value7,
        [TableColumn8] = @Value8,
        [TableColumn9] = @Value9,
        [TableColumn10] = @Value10,
        [TableColumn11] = @Value11,
        [TableColumn12] = @Value12,
        [TableColumn13] = @Value13
    WHERE [TableColumn] = @Value

END
GO`

With enough defaults on a table, you can simply say:

INSERT t DEFAULT VALUES

Note that this is quite an unlikely case, however.

I've only had to use it once in a production environment. We had two closely related tables, and needed to guarantee that neither table had the same UniqueID, so we had a separate table which just had an identity column, and the best way to insert into it was with the syntax above.

The most succinct solution I could come up with is to follow the insert with an update for the column with the default:

IF OBJECT_ID('tempdb..#mytest') IS NOT NULL DROP TABLE #mytest
CREATE TABLE #mytest(f1 INT DEFAULT(1), f2 INT)
INSERT INTO  #mytest(f1,f2) VALUES (NULL,2)
INSERT INTO  #mytest(f1,f2) VALUES (3,3)

UPDATE #mytest SET f1 = DEFAULT WHERE f1 IS NULL

SELECT * FROM #mytest

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

Cheers...