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

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.

Do you know the names of the databases in advance, or are they potentially variable? — Paul Williams, Dec 09 '13 at 17:25
@Paul Williams. I do. It will be either DB1 or DB2. No other possibilities. — Dzmitry Sevkovich, Dec 09 '13 at 17:32

D Stanley · Accepted Answer · 2013-12-09T17:28:48.333

3

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;

edited Dec 09 '13 at 17:28

answered Dec 09 '13 at 17:16

D Stanley

149,601
11
178
240

So basically, if I have 100 references to one of these db's, I have to use that if-else for each reference. Correct? – Dzmitry Sevkovich Dec 09 '13 at 17:22
@DzmitrySevkovich if all of the queries are in the same scope than you can use a `USE` statement to change the database - see my edit. – D Stanley Dec 09 '13 at 17:29
@D Stanley unfortunately, it gave me an error: a USE database statement is not allowed in a procedure, function or trigger. – Dzmitry Sevkovich Dec 09 '13 at 17:42
Can you choose the database before running the procedure? The procedure should then run using that database. – D Stanley Dec 09 '13 at 19:25

score 2 · Answer 2 · answered Dec 09 '13 at 17:18

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

answered Dec 09 '13 at 17:18

Paul Williams

16,585
5
47
82

Rahul Tripathi · Answer 3 · 2013-12-09T17:26:31.903

1

You may try like this using the DB_ID function:

IF DB_ID('DB1') IS NOT NULL
PRINT 'exists'

Try like this:

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

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

edited Dec 09 '13 at 17:26

answered Dec 09 '13 at 17:15

Rahul Tripathi

168,305
31
280
331

I actually used it. But I don't need to just check if it exists, but I want to use the name of existing db. – Dzmitry Sevkovich Dec 09 '13 at 17:16
@DzmitrySevkovich:- I have updated my answer. Hope that helps! – Rahul Tripathi Dec 09 '13 at 17:17
1

`ISNULL(DB_ID('DB1'), 'DB2')` will try and cast the string `DB2` to an int if `DB1` does not exist. – Martin Smith Dec 09 '13 at 17:20
@MartinSmith:- Sir I didnt get that very clearly. Am I missing something. It would be great if you can edit my answer if that is wrong! :) – Rahul Tripathi Dec 09 '13 at 17:22
@RahulTripathi: if you try to run that query, you get the error "Conversion failed when converting the varchar value 'DB2' to data type smallint." – Paul Williams Dec 09 '13 at 17:24
@RahulTripathi - The most obvious way to fix it is in D Stanley's answer already. – Martin Smith Dec 09 '13 at 17:27
@MartinSmith:- Yes I have done that. But D Stanley was more quick. But I have updated my answer as well! Thanks – Rahul Tripathi Dec 09 '13 at 17:28

score 0 · Answer 4 · answered Dec 09 '13 at 17:30

DECLARE @dbName NVARCHAR(128);
DECLARE @Sql NVARCHAR(MAX);
SET @dbName = 'DataBaseName'


IF db_id(@dbName) is not null

   BEGIN    
       SET @Sql = N'SELECT * FROM ' + QUOTENAME(@dbName) + N'.[dbo].[TableName]'   
   END

ELSE

  BEGIN
    SET @Sql = N'SELECT * FROM ' + QUOTENAME(db2Name) + N'.[dbo].[TableName]'
  END 

EXECUTE sp_Executesql @Sql

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

4 Answers4