AndreMiranda AndreMiranda - 1 month ago 12
SQL Question

How can I alter this computed column in SQL Server 2008?

I have a computed column created with the following line:

alter table tbPedidos
add restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 then 1 else 0 end as bit))


But, now I need to change this column for something like:

alter table tbPedidos
alter column restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1 then 1 else 0 end as bit))


But it's not working. I'm trying to input another condition to the case statement, but it's not working.

Thanks a lot!

Answer

If you're trying to change an existing column, you can't use ADD. Instead, try this:

alter table tbPedidos alter column restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1 then 1 else 0 end as bit))

EDIT: The above is incorrect. When altering a computed column the only thing you can do is drop it and re-add it.