4

Using SQL Server 2012.

I have a column in my member table that is created using the following script: 

[EnableMemberWebAccess] [bit] NOT NULL DEFAULT ((1))

I want this column to only have 1 as a default if another column in this same table is a certain value. Let's call this column MemDesc, and I want the EnableMemberWebAccess to be defaulted to 1 when MemDesc = 'Founder', and for it to default to 0 when MemDesc != 'Founder'.

Any help much appreciated!

BenW2811
  • 81
  • 6
  • It looks like you need to write a trigger: http://stackoverflow.com/questions/1744455/using-udf-for-default-value-of-a-column , http://stackoverflow.com/questions/30402167/use-a-udf-as-the-default-value-in-a-table-column-in-sql-server – Vladimir Baranov Mar 31 '16 at 01:12

2 Answers2

1

There is probably no way to achieve a default value that can be changed afterwards. Either you have a value that you insert in the beginning. You will then need to take care of consistency within the application:

ALTER TABLE *table* ADD COLUMN EnableMemberWebAccess bit NULL

UPDATE *table* SET *table.*EnableMemberWebAccess = CAST(CASE WHEN *table*.MemDesc = 'Founder' THEN 1 ELSE 0 END AS bit)

ALTER TABLE *table* ALTER COLUMN EnableMemberAccess bit NOT NULL

Or you have to use a computed column. This will not allow you to change the value of the column except if you change the value of the column it depends on.

timcbaoth
  • 669
  • 1
  • 7
  • 12
  • 1
    This will be a one time update whereas the question talks about default value to the column. How will this cater for new records inserted after the update? – Amit Sukralia Mar 30 '16 at 23:19
  • After the update it will not do anything, other than fail. I think there is no way of doing what the op asked. At least I do not know of any way. Providing consistency of the column should be done on the application level, not on the database. If the value should be the same all along, a calculated column would be needed. – timcbaoth Mar 30 '16 at 23:21
  • @Amit Sukralia What the question asks cannot be done by a calculated column. A calculated column will not be modifiable. A default value is. However a default value cannot depend on another column, as far as I know. – timcbaoth Mar 30 '16 at 23:27
  • Try the code that I have given in my answer and that should show that it works. What do you mean by "A calculated column will not be modifiable". – Amit Sukralia Mar 30 '16 at 23:30
  • @AmitSukralia I cannot do "UPDATE *table* SET EnableMemberWebAccess = 1 WHERE *whatever* – timcbaoth Mar 30 '16 at 23:34
  • 1
    Yes you are correct - It will not be updateable. However, is that even required. I would assume that `EnableMemberWebAccess` will always be dependent on `MemDesc`. Therefore, whenever there is a change in the value of `MemDesc`, `EnableMemberWebAccess` should change accordingly. If this is not the expected behaviour then computed column approach will not suffice. – Amit Sukralia Mar 30 '16 at 23:46
-1

Computed column should work for you:
ADD EnableMemberWebAccess AS cast((CASE WHEN MemDesc='Founder' THEN 1 ELSE 0 END) as bit)

Amit Sukralia
  • 950
  • 1
  • 5
  • 13