How to check which database exists and use it in the function? [duplicate]

Question

Given different machines with sql server. All of them have the same databases. The only difference between these db's is their name. It could be two names, let's say 'DB1' and 'DB2'.

I need to check, which name is used on the given machine and create a function over it. The function is pretty big, it has at least 50 spots where the name of the DB is needed.

I was trying to do something like

DECLARE @dbName VARCHAR(20)
SET @dbName = ISNULL(DB_ID('DB1'), 'DB2');

SELECT * FROM @dbName.dbo.testtable;

But it does not work. Any help would be appreciated.

Answer 1

No, that won't work. With only two possible database you may be better off with an if:

DECLARE @dbName VARCHAR(20)
SET @dbName = CASE WHEN DB_ID('DB1') IS NULL THEN 'DB2' ELSE 'DB1' END;

IF @dbName = 'DB1' 
    SELECT * FROM DB1.dbo.testtable;
ELSE
    SELECT * FROM DB2.dbo.testtable;

If you want to run ALL future queries in that scope against that database then dynamically run a USE statement instead:

IF @dbName = 'DB1' 
    USE DB1;
ELSE
    USE DB2;

Answer 2

You can use dynamic SQL:

DECLARE @dbName VARCHAR(20)
SET @dbName = 'DB2'

IF DB_ID('DB1') IS NOT NULL
    SET @dbName = 'DB1'

DECLARE @SQL NVARCHAR(100)
SET @SQL = N'SELECT * FROM ' + @dbName + N'.dbo.testtable'

EXEC sp_executesql @SQL