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?

CodePudding user response：

SQL Server compiles all the statements in a batch before executing.

This will be a compile time error and stop the batch compiling (referencing non existent objects can cause the statement to have deferred compilation where it tries again at statement execution time but this does not apply here).

If the batch may need to run such problem statements you need to move them into a different scope so they are only compiled if they meet your flow of control conditions.

Often the easiest way of doing that is to wrap the whole problem statement in EXEC('')

IF OBJECT_ID('dbo.CL_Example', 'U') IS NOT NULL /*It is a table*/
    EXEC('UPDATE dbo.CL_Example ....')

Or use sys.sp_executesql if it isn't just a static string and you need to pass parameters to it.

CodePudding user response：

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.