Add default value to an existing column based on If Then Else sql server 2008

Question

Is it possible to alter an existing column to accept a default value based on another column value?

For Eg:

Create table #Temp
(
    TempId int not null
   ,TransCode int    --values of 1 / 2 or 3
   ,ReasonCode int
)

The default value for ReasonCode is based off TransCode. When TransCode is 1 and ReasonCode is Null then 99 else ReasonCode

How do I add this default constraint?

Answer 1

You can do it via CHECK constraint,

First you will have to create table,

 create table myTemp1(TempId int not null ,TransCode int,ReasonCode int);

and then add the constraint as

create table myTemp1(TempId int not null ,TransCode int,ReasonCode int);

alter table myTemp1
add constraint check_role CHECK(case when (TransCode = 1 AND ReasonCode = NULL)
                                then 99 else ReasonCode end = ReasonCode);

OR like

alter table myTemp1
add constraint check_role CHECK(ReasonCode = (case when (TransCode = 1 AND ReasonCode
                                = NULL) then 99 else ReasonCode end = 1))

demo at http://sqlfiddle.com/#!3/d633a/1

Answer 2

But a Check Constraint will just check your entries and not put a default value. This is my exact same problem. However, I researched that a Calculated Column will do the trick.