SQL Server - I want to select a database based on the identifier used

Question

I have several databases. Can I hit one of the databases based on an identifier that is dynamic? for example I have three databases DB1,DB2,DB3 .

I have a select * from tblEmployees . (This table is present in all three databases). I have an identifier (or some variable) whose value can be 1 or 2 or 3 and based on the value of this variable, which I get dynamically when my service arrived, I would like to select the database from which the values ​​should be obtained.

Can this be done? My DB is SQL Server and the front end is asp.net .

My connection strings are stored in the web.config file. Can I have several connection strings that will have the same server with diff db names and select one of them based on the identifier.

Answer 1

1. In the real world, most of the time you should store your connection strings in the web.config file. therefore, you can leave three connection strings that will have the same server but a different database name, and then you can select one of the connection strings to connect your application to the required database.

2. If you can build this connection string at runtime.

using this technique, you will never have to write 2 or more queries, just changing the query string, and your queries will work for all databases.

Answer 2

You can do it as follows:

 if(val == 1) { select * from [DB1].[dbo].[tblEmployees] } else if(val == 2) { select * from [DB2].[dbo].[tblEmployees] } 

Answer 3

Try this option -

 DECLARE @ID INT SELECT @ID = 2 DECLARE @SQL NVARCHAR(500) SELECT @SQL = 'SELECT * FROM DB' + CAST(@ID AS CHAR(1)) + '.dbo.tblEmployees' PRINT @SQL EXEC sys.sp_executesql @SQL 

Output -

 SELECT * FROM DB2.dbo.tblEmployees 

Answer 4

T-SQL method:

 declare @db int if @db = 1 begin use [db1] select * from tblEmployees end if @db = 2 begin use [db2] select * from tblEmployees end -- and so on 

Answer 5

IMO will best use a different connection string to achieve multi-user protection against similar databases. Ideally, ignore the code so that most of your code does not need it, but just does:

 using(var conn = Somewhere.GetOpenConnection()) { // ... } 

or worst case scenario:

 using(var conn = Somewhere.GetOpenConnection(Environment.Published)) { // ... } 

(here Environment is enum that various databases represent)

where GetOpenConnection determines which database is needed and either searches for the construction of the correct connection string.

But specifically:

• you cannot parameterize the database name in the query
• using use between operations would be a very bad idea in terms of reusing a connection
• you can explicitly use three-part identifiers (i.e. DB1..SomeTable or DB1.dbo.SomeTable ), but this naturally does not scale for a large number of databases.