How to update a table and ignore a computed column if it is computed but update it if it is not computed

Question

I have different versions of SQL Server databases:

• in DB 1, column 'IsReadOnly' is a computed column
• in DB 2, column 'IsReadOnly' is a NON computed column

I need to create a standard script which will work on both versions:

IF EXISTS (SELECT 1 FROM sys.columns 
           WHERE Name = N'IsReadOnly'
             AND Object_ID = OBJECT_ID(N'dbo.TableA')
             AND is_computed = 0) 
BEGIN
    UPDATE TableA   
    SET IsReadOnly = @IsReadOnly    
    WHERE Id = @ID 
END

When running the above, it works on the version where is_computed = 0. But when running on the version where is_computed = 1 I get:

Msg 271, Level 16, State 1, Line 322
The column "IsReadOnly" cannot be modified because it is either a computed column or is the result of a UNION operator

Any help very much appreciated. Thanks

Answer 1

This is a compile-time versus execution-time issue. The error is occurring at compile-time on the database where the value cannot be updated.

You can get around it using dynamic SQL:

IF EXISTS( SELECT 1 FROM sys.columns 
           WHERE Name = N'IsReadOnly'
           AND Object_ID = Object_ID(N'dbo.TableA')
           AND is_computed = 0          
          ) 
BEGIN
    EXEC sp_execute_sql N'
Update TableA    
   Set IsReadOnly = @IsReadOnly     
   Where Id = @ID',
                       N'@IsReadOnly int, @ID int',  -- guessing at the types
                       @IsReadOnly=@IsReadOnly, @ID=@ID;
END

A simpler method, though, might be a TRY/CATCH block:

BEGIN TRY
    Update TableA    
       Set IsReadOnly = @IsReadOnly     
       Where Id = @ID ;
END TRY
BEGIN CATCH
    -- Check for the particular error and do something when that occurs
END CATCH;