Post reply

how to Force Default Value in case of Null insertion.

  • September 14, 2011 at 3:13 am

    #390401

    I have table that has two Columns, ID and price.

    I have default Constraint on Price Cloumn of 0.

    When I insert Nothing in Price Column then it inserts Default value of 0 If I insert Explicitly null value in price Column from application Then It will insert Null value .I want to force default Value of 0 in case of NULL also.

    Any solution Please.

    Sample Code is given below

    create Table CheckConstraint

    (ID int primary key,

    Price decimal default 0 )

    GO

    --CASE 1

    insert into CheckConstraint(ID)

    values(1)

    --Result 1,0

    --CASE 2

    insert into CheckConstraint(ID,Price)

    values(2,null)

    --Result

    2NULL

    I want Result as 2,0

    Thanks

  • September 14, 2011 at 3:19 am

    #1381124

    I would make the column NOT NULL and would try to improve your input. Of course you could do this also with a trigger or something, but I avoid myself triggers at all cost.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • September 14, 2011 at 4:01 am

    #1381140

    insert into #CheckConstraint(ID,Price)

    values(2,default)

    can you change null value to default in Application?

    I Have Nine Lives You Have One Only
    THINK!

  • September 14, 2011 at 4:41 am

    #1381155

    Actually we are using isnull too much in sps for reports which is overhead for performance. We are going to remove isnull by using default constraint. At application level it would be difficult to find every place where null is inserting rather it is easy at DB levels.

    thanks

  • September 14, 2011 at 5:03 am

    #1381170

    Hi,

    Prior to insertion we can check ISNULL(@Price).

    IF ISNULL(@Price)

    SET @Price = 0

    Thanks

    Shatrughna

  • September 14, 2011 at 5:16 am

    #1381176

    azhar.iqbal499

    in this case, you have to use "insted of" triger

    I Have Nine Lives You Have One Only
    THINK!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply