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:
default value in multiple places, andinsert 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.
This question is related to
sql
sql-server
tsql
stored-procedures
null
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.
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
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
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.
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.
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.
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.
.
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.
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`
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)
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 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.
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 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.
.
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.
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...
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.
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.
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.
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
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.
Try an if statement ...
if @value is null
insert into t (value) values (default)
else
insert into t (value) values (@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.
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.
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
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.
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.
.
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
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...
Try an if statement ...
if @value is null
insert into t (value) values (default)
else
insert into t (value) values (@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.
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
Cheers...
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...
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.
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...
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.
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') )
Try an if statement ...
if @value is null
insert into t (value) values (default)
else
insert into t (value) values (@value)
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.
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.
.
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') )
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`
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...
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
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') )
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
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') )
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.
Source: Stackoverflow.com