Modify the computed expression of computed column

[indian143]

Hi All, I have a database table which I want to modify the computed expression of a column, Computed expression of the column was as below

(case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0) when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then [AnnualFederalPovertyLevel] when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then [MonthlyFederalPovertyLevel] when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then [MonthlyFederalPovertyLevel] else [AnnualFederalPovertyLevel] end)

Now I want to change it to

(case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0)
when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then
(CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END)
when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
else (CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END) end)

I have tried in the below way, but it didn't work gave me the following error

ALTER TABLE dbo.ApplicationData DROP COLUMN ReportingFpl;
GO
ALTER TABLE dbo.ApplicationData ADD ReportingFpl AS (case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0)
when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then
(CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END)
when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
else (CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END) end);

Error

Msg 5074,

[Richard Deeming]

As the first error says, you have an index on the table called IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints which uses the computed column, so you can't drop the column without dropping the index first.

1. Find any indexes which use the column;
2. Generate both a drop and a create script for each affected index;
3. Run the index drop scripts;
4. Drop your column;
5. Add the new version of the column;
6. Then run the index create scripts.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[deepakdynamite]

Hello, I don't think it will allow you to change Computer column expression on the Fly... Instead add new column with new expression and name it as newColumn. Delete you old column and then rename newColumn to Original column name using below command.

sp_RENAME 'Table_First.newColumn', 'YourOriginalColumnName' , 'COLUMN'