Insert default value in a table when data or parameter or value is null in a not null column in sql server.
Suppose we have created a table TestDefault by following sql query:
CREATE TABLE TestDefault(
Id INT IDENTITY
PRIMARY KEY,
Value INT NOT NULL DEFAULT(1),
Remarks VARCHAR(100) NULL
)
We can insert default value in column value in following ways:
1.
INSERT INTO TestDefault DEFAULT VALUES
2. INSERT INTO
TestDefault(Value,Remarks) VALUES(DEFAULT, 'Exact Help')
3. INSERT INTO
TestDefault(Remarks)
VALUES('Exact Help')
But when we will try to insert NULL
in column Value we will get error message. For example:
DECLARE @Data AS INT = NULL
INSERT INTO TestDefault(Value) VALUES(@Data)
Error message will
something like this: Cannot insert the value NULL into column '', table '';
column does not allow nulls. INSERT fails.
Solution 1:
We can insert own
default value when data is null. For example:
DECLARE @Data AS INT = NULL
INSERT INTO TestDefault(Value) VALUES(ISNULL(@Data,1))
Solution 2: If we
want to insert default value of column then we can do in this way:
DECLARE @Data AS INT = NULL
INSERT INTO TestDefault(Value) VALUES(ISNULL(@Data,dbo.DefaultVal('Value','TestDefault')))
For this fisrt you
have to create user defined function DefaultVal. Which will something like
this:
CREATE FUNCTION DefaultVal(
@ColumnName AS VARCHAR(100),
@TableName AS VARCHAR(100)
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @DefaultValue AS VARCHAR(200)
SELECT @DefaultValue =
dc.definition
FROM sys.columns c INNER JOIN sys.default_constraints dc
ON c.default_object_id =
dc.object_id
WHERE c.object_id = object_id(@TableName) AND c.name = @ColumnName
SET
@DefaultValue = REPLACE(REPLACE(@DefaultValue,')',''),'(','')
RETURN
@DefaultValue
END
Note: This
function is not useful if default value is complex expression like ((5)+(3))
or default value of character type column keeps ')' or '('.
Writing dynamic sql queries in sql server
Difference between temporary table and table variable in sql server
Difference between varchar and nvarchar in sql server.
Difference between char and varchar in sql server.
Variables in sql server 2008 R2
BETWEEN keyword in sql server
How to use variable in ORDER BY clause in sql server.
Writing dynamic sql queries in sql server
Difference between temporary table and table variable in sql server
Difference between varchar and nvarchar in sql server.
Difference between char and varchar in sql server.
Variables in sql server 2008 R2
BETWEEN keyword in sql server
How to use variable in ORDER BY clause in sql server.