Post reply

Conditional Default value for a column

  • November 6, 2013 at 11:03 pm

    #285833

    Dear All

    Whant to assign conditional default value during table creation Like

    create table aa

    ( cc int null ,

    bb int default (case when cc = 1 then 1 else 2 end )not null

    )

    Is it possible?

  • November 7, 2013 at 12:17 am

    #1664637

    No. Just try it !

    You'll get an error message :

    Msg 128, Level 15, State 1, Line 3

    The name 'cc' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    which is very explicit ;-).

    You can use a trigger.

  • November 7, 2013 at 3:38 am

    #1664691

    I know its not working but gave exmaple to explain what i want 🙂

  • November 7, 2013 at 3:45 am

    #1664694

    Defaults cannot reference column names. Per BoL:

    Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default.

    Use a trigger, or if that 1 or 2 is permanent value, use a calculated column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2013 at 3:56 am

    #1664697

    thanks i had forgoten about computed column

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

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