SQL: IF OBJECT_ID() and IF EXISTS don’t work in conjunction with UPDATE statement

I am using SQL Server 2017, I have a View called Foo, what I’m trying to do is to check if Foo is a user-defined table, if Yes, do an UPDATE, else do not do anything.

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('Foo') AND xtype = 'U')
   UPDATE Foo SET .....

Here I check if the Foo exist and if it is a user-defined table (xtype='U'). Since Foo is a View, so xtype='U' should be false and the UPDATE should not execute, but the problem is that the UPDATE still execute, and error occurred.

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

When I execute the sql query below, indeed there is no record is returned, so the EXISTS should return false.

SELECT * FROM sysobjects WHERE id = OBJECT_ID('Foo') and xtype = 'U'

I’ve also tried this, but same problem, UPDATE still execute.

IF OBJECT_ID('Foo', 'U')
   UPDATE Foo SET .....

But, if I change the UPDATE statement into say a PRINT statement, then it works fine.

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('Foo') and xtype = 'U')
   PRINT 'A'

In this case, A is not printed, and the command executed successfully.

Commands completed successfully.

So, my question is why the IF EXISTS or IF OBJECT_ID I used above don’t work with UPDATE? Am I missing something or I am doing it wrong?

Thanks.

Answer

You can’t put the update directly behind the IF OBJECT_ID… however, you can execute the SQL this way:

IF OBJECT_ID(N'[dbo].[...]', N'U') IS NOT NULL
  EXEC('UPDATE...')
GO