Converting computed columns to fixed

Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John