SQL Server gives error to unreachable code

Question

We have the following case:

1. We have a SQL Server database with a table CL_Example and another database with a view with the same name CL_Example. This view is created by using more than one table with inner join.

2. The structure of the CL_Example view and CL_Example table is the same.

3. We have a SQL script which will be executed on both of these databases. In case it finds CL_Example as a table, it should insert the data, and if it finds CL_Example as view, then it should not insert the data.

But when we are executing the script on the database where CL_Example is a view, we get the following error:

Update or insert of view or function 'dbo.CL_Example' failed because it contains a derived or constant field.

This bug is getting generated even if the insert statement is unreachable for the database where CL_Example is the view. Can we prohibit this error and continue to execute the script in both databases?

Answer 1

May I suggest another approach to your problem. Instead of fixing query to not insert if CL_Example is view, you can create trigger on view INSTEAD OF INSERT that does nothing and you don't have to worry about insert queries.

EXAMPLE:

create view vw_test 
as 
select 1 as a, 2 as b;
go

select * from vw_test; 
go

if (1=2)
begin 
  insert into vw_test (a,b) values (3,4); -- unreachable but fails
end 
go

create trigger tg_fake_insert on vw_test
instead of insert
as 
begin
set nocount on;
end;
go

if (1=2)
begin
    insert into vw_test (a,b) values (3,4); --unreachable, does not fail
end
else 
begin 
   insert into vw_test (a,b) values (3,4); --actually works
end
go 

You might even want to put some actual logic in that trigger and divert data somewhere else?

EDIT: To put additional thought in - if you are using view to replace table in order for legacy code to work - you SHOULD handle insert/update/delete situations that may occur and good way to do that is with instead of triggers.