How to edit a formula of a computed column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to edit a formula of a computed column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-28 : 07:55:01

Pushpendra writes "Hi,

I have been trying to find a way to change the formula associated to a computed column in a table. Specifically my table has a TOTAL column that is the sum of all the other columns. This table is dynamic, so the user can add a new column to this table. At this point I need to change the formula of teh TOTAL column so that the total includes the values added to the new column.

It would be great if you could direct me to some way of achieving this functionality (either using Transact SQL or SQL DMO or any other approach).

Thanks,

Pushpendra."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 13:53:38

[code]create table t99(c1 int, c2 int, ctotal as(c1+c2))
insert t99 select 1,2

select * from t99

alter table t99 add c3 int default(0) with values
go

alter table t99 drop column ctotal
alter table t99 add ctotal as(c1+c2+c3)

select * from t99

drop table t99[/code]

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-28 : 15:03:37

quote:
Originally posted by AskSQLTeam

Pushpendra writes "Hi,

I have been trying to find a way to change the formula associated to a computed column in a table. Specifically my table has a TOTAL column that is the sum of all the other columns. This table is dynamic, so the user can add a new column to this table. At this point I need to change the formula of teh TOTAL column so that the total includes the values added to the new column.

It would be great if you could direct me to some way of achieving this functionality (either using Transact SQL or SQL DMO or any other approach).

Thanks,

Pushpendra."

Don't ever do this. Columns should not be dynamically added to tables:

http://www.datamodel.org/NormalizationRules.html

If you want formulas to be returned based on the values in tables, then that's what SELECT statements are for. Even Dynamic SQL incorporating the users' custom formulas is a MUCH better solution than constantly ALTER'ing tables.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources