asked
IF STATEMENT ON 2 SELECT QUERYS
I don't know what function to use I think its going to be either EXISTS, EXEC or object_name to do the following but I am not sure the best way of doing my query and hope someone can help. I want to run a query that does a basic select query but if the database does not exist then use a different database. Basic Example below. `/*If select query for DBa fails due to not existing then use DBb*/` `SELECT Name from Database_01012017.dbo.TblA` `SELECT Name from Database_01122016.dbo.TblA`
SO something like IF EXISTS (SELECT * FROM sys.databases WHERE name = N'' BEGIN SELECT Name FROM db1.dbo.TblA END ELSE BEGIN SELECT Name FROM db2.dbo.TblA END Except, of course, that that will cause compilation errors because it'll try to build a query plan for a database that doesn't exist. So what you need to do is something more like: DECLARE @sql varchar(max) DECLARE @dbResult table (name varchar(max)) IF EXISTS (SELECT * FROM sys.databases WHERE name = 'db1') BEGIN SELECT @sql = 'SELECT name from db1.dbo.tblA' END ELSE BEGIN SELECT @sql = 'SELECT name from db2.dbo.tblA' END INSERT INTO @dbResult EXEC (@sql) And then all you have to do is process the name field in the @dbResult table variable. NB: code untested, but the concept is one I've used before...
Thanks for this while looking into this for quite a while I thought of the following however I am unsure which query would work best. EXEC sp_executesql N' IF EXISTS(select * from sys.databases where name = ''db1'') SELECT * FROM [db1].[dbo].tblA IF EXISTS(select * from sys.databases where name = db2'') SELECT * FROM [db2].[dbo].tblA ' or very similar to the above if exists(select * from sys.databases where name = db1') SELECT * FROM [db1].[dbo].tblA ELSE SELECT * FROM [db2].[dbo].tblA
18 People are following this question.
