Jan 27, 2014

Insert default value when data or parameter is null in sql server.


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

No comments:

Post a Comment