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
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.