Dynamically set database name in SQL Server stored procedure?

Question

How to set dynamic database name in SQL Server stored procedure?

Answer 1

Stored procedures are database dependent. If you want to dynamically access data from another database, you will have to create dynamic SQL and execute it.

Declare @strSQL VarChar (MAX) Declare @DatabaseNameParameter VarChar (100) = 'MyOtherDB' SET @strSQL = 'SELECT * FROM ' + @DatabaseNameParameter + '.Schema.TableName' 

You can use if commands to set @DatabaseNameParameter to the database to your liking.

Execute the statement to get the results.

Answer 2

Sometimes using SYNONYM is a good strategy:

 CREATE SYNONYM [schema.]name FOR [[[linkedserver.]database.]schema.]name 

Then refer to the object by its synonym in the stored procedure.

Changing where synonym points are is a matter of dynamic SQL, but then your main stored procedures can be fully dynamic SQL-free. Create a table to manage all the objects you need for the link and the stored procedure that switches all the necessary synonyms into the correct context.

This feature is only available in SQL Server 2005 and later.

This method is NOT suitable for frequent switching or for situations where different connections must use different databases. I use it for a database that occasionally moves between servers (it can run in the prod database or in the replication database, and they have different names). After restoring the database in her new home, I launch my switcheroo SP switch and everything works after about 8 seconds.

Answer 3

This is not dynamic SQL and works for stored procedures.

 Declare @ThreePartName varchar (1000) Declare @DatabaseNameParameter varchar (100) SET @DatabaseNameParameter = 'MyOtherDB' SET @ThreePartName = @DatabaseNameParameter + '.Schema.MyOtherSP' EXEC @ThreePartName @p1, @p2... --Look! No brackets