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    maintain the default value in multiple places  and 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

User · Answer

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

User · Answer

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

User · Answer

The easiest way to do this is to modify the table declaration to be   CREATE TABLE Demo       MyColumn VARCHAR 10  NOT NULL DEFAULT  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

User · Answer

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

User · Answer

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   maintain the default value in multiple places  and  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

User · Answer

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

User · Answer

The easiest way to do this is to modify the table declaration to be   CREATE TABLE Demo       MyColumn VARCHAR 10  NOT NULL DEFAULT  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

User · Answer

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   maintain the default value in multiple places  and  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The easiest way to do this is to modify the table declaration to be   CREATE TABLE Demo       MyColumn VARCHAR 10  NOT NULL DEFAULT  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

User · Answer

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

User · Answer

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

User · Answer

Try an if statement       if  value is null      insert into t  value  values  default  else     insert into t  value  values   value

User · Answer

Try an if statement       if  value is null      insert into t  value  values  default  else     insert into t  value  values   value

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The easiest way to do this is to modify the table declaration to be   CREATE TABLE Demo       MyColumn VARCHAR 10  NOT NULL DEFAULT  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

User · Answer

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

User · Answer

Try an if statement       if  value is null      insert into t  value  values  default  else     insert into t  value  values   value

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try an if statement       if  value is null      insert into t  value  values  default  else     insert into t  value  values   value

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt default value gt      IsNull column2   lt default value gt         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

User · Answer

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   lt default value gt      IsNull column2   lt default value gt         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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Insert default value when parameter is null

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to null