Update Rows From Table If Table Exists Without Using sp_executesql

Question

I have a situation where I need to run a query on a large number of similar databases. Most of them will have a table that I need to update some rows in but some of them will not have the table.

I want to write a query that has an IF statement checking if the table exists without needing to use EXEC sp_executesql for the UPDATE statement. Is there a way to do this in SQL Server?

Here is an example of what I'm doing today:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MyTableName]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  -- this is what I want to be able to do but cannot
  UPDATE [dbo].[MyTableName] SET [SomeColumn] = NULL WHERE [SomeColumn] NOT IN (1, 2, 3)

  -- this is the only way I know to do this without an error
  EXEC sp_executesql N'UPDATE [dbo].[MyTableName] SET [SomeColumn] = NULL WHERE [SomeColumn] NOT IN (1, 2, 3)'
END

I may have more copies of this if statement checking for different tables tables existing and updating them but no other logic in the batch. Without sp_executesql it gives me an error about the table not existing because it needs to compile the entire batch before evaluating the IF statement.

I'm not opposed to sp_executesql specifically, but rather dynamic SQL generally. I would like a solution that doesn't require me to encode/escape my inner query as a string and allows me to use an IDE to provide syntax highlighting and autocompletion while writing the inner query.

Answer 1

The issue here is that SQL Server validates the objects when it parses the SQL statement and so if the object doesn't exist it fails, however, there is a trick to getting around this.

SQL Server parses object names in the IF statement, but does not validate objects in the ELSE portion. Instead of checking if the object exists and then taking action if it does, you check if the object does not exist, then take action if it does.

Example:

CREATE TABLE TestTable1 (RowData VARCHAR(255))
GO
INSERT INTO TestTable1 VALUES ('Test data')
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TestTable2')
BEGIN
    PRINT 'Table does not exist'
END
ELSE
BEGIN
    UPDATE TestTable2
    SET RowData = 'Text update'
END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TestTable1')
BEGIN
    PRINT 'Table does not exist'
END
ELSE
BEGIN
    UPDATE TestTable1
    SET RowData = 'Text update'
END

In the example, TestTable2 doesn't exist, however, the script still executes because the missing table is referenced in the ELSE portion of the IF statement. This allows the script to be parsed and when it is executed it never reaches the portion of the script with the missing object and so does not cause an error.