Catch Update/Insert Computed Column of a View

Question

I'm using SQL Server 2005/2008 R2

My SQL script is:

UPDATE MyView 
SET MyColumn = 'My new value' 
WHERE ID = 7

The error message:

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

I know that MyColumn is a computed column from the base table (like when FullName is computed from LastName + ', ' + FirstName)

My aim is to catch the error so the script keeps executing.

I tried the following script with no luck. The error is not caught:

BEGIN TRY
    UPDATE MyView 
    SET MyColumn = 'My new value' 
    WHERE ID = 7
END TRY 
BEGIN CATCH
    -- Error occurred while updating view. The script will keep running
END CATCH

I checked the following scripts trying to solve the problem, with no luck:

Script 1

SELECT is_computed 
FROM sys.columns c, sys.views v
WHERE c.object_id = v.object_id
AND v.name = 'MyView'

The previous script returns 0 for all results (which is not correct, one of the column is a computed column)

Script 2:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'MyView'`

Also I couldn't find any field from the returned script that has something to do with 'computed column' or 'result of a union' operation.

There is `sys.computed_columns` which **exactly** tells you which columns are computed - just do a `SELECT Name FROM sys.computed_columns` to get the names of the computed columns — marc_s, Mar 22 '13 at 23:06

score 0 · Answer 1 · answered Mar 22 '13 at 20:40

0

First of all, no system views or tables can really tell you which column of your view is derived column or computed column. It almost can only be detected during run time or you can use powershell script or something else to detect it outside of SQL.

If you just want to continue your script after exception happen, you can use goto, but that is not good practice.


begin try
   select 1/0
end try
begin catch
   Print 'error occurred'
   goto MarkPoint
end catch
select * from sys.columns
MarkPoint: 
  select top 1 * from sys.tables

answered Mar 22 '13 at 20:40

ljh

2,546
1
14
20

2

**OF COURSE THERE IS A WAY!!** There is `sys.computed_columns` which **exactly** tells you which columns are computed ... also, there's a `is_computed` flag on every entry in `sys.columns`, too .... – marc_s Mar 22 '13 at 23:05
No marc, views fields don't appear as "is_computed = 1". They are always 0. I mentioned that in my question. ljh, the error IS NOT CATCHED. And this is my whole problem. I mentioned that in the question. – yazanpro Mar 22 '13 at 23:24
@marc_s what are these computed_column s are for? – Juliyanage Silva Jan 12 '23 at 13:55
1

@JuliyanageSilva: it's a SQL Server catalog view that shows all the relevant info for the computed columns in your SQL Server tables – marc_s Jan 12 '23 at 15:34

Kyle Hale · Answer 2 · 2013-03-22T20:51:52.203

Not that it helps you, but if you're running SQL Server 2012 you can run

sp_describe_first_result_set N'select * from View'

And it will provide the correct info (namely, is_updateable is set to 0 and is_computed_column is set to 1).

Here is a not entirely 100% reliable query that will return all the columns in your view which are actually computed columns in the base table:

    SELECT c.name FROM sys.objects AS O
   INNER JOIN sys.sql_expression_dependencies SED ON SED.referenced_id=O.object_id
    INNER JOIN sys.objects O2 ON O2.object_id=SED.referencing_id
    INNER JOIN sys.columns c on c.object_id = o.object_id
    where
     QUOTENAME(O2.name) = 'MyView'
     and c.is_computed = 1

It's not 100% reliable because that sql_expression_dependencies table doesn't keep itself entirely up to date, and can't "read" queries perfectly (and it misses things like function calls within function calls, etc.) But for a standard "view = columns from a table" object, it will work fine.

Catch Update/Insert Computed Column of a View

2 Answers2