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.