[ACCEPTED]-Set database name dynamically in SQL Server stored procedure?-sql-server
Sometimes, the use of SYNONYMs is a good 20 strategy:
CREATE SYNONYM [schema.]name FOR [[[linkedserver.]database.]schema.]name
Then, refer to the object by its 19 synonym in your stored procedure.
Altering 18 where the synonym points IS a matter of 17 dynamic SQL, but then your main stored procedures 16 can be totally dynamic SQL-free. Create 15 a table to manage all the objects you need 14 to reference, and a stored procedure that 13 switches all the desired synonyms to the 12 right context.
This functionality is only 11 available in SQL Server 2005 and up.
This 10 method will NOT be suitable for frequent 9 switching or for situations where different 8 connections need to use different databases. I 7 use it for a database that occasionally 6 moves around between servers (it can run 5 in the prod database or on the replication 4 database and they have different names). After 3 restoring the database to its new home, I 2 run my switcheroo SP on it and everything 1 is working in about 8 seconds.
Stored Procedures are database specific. If 5 you want to access data from another database 4 dynamically, you are going to have to create 3 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 2 clauses to set the @DatabaseNameParameter to the DB of your liking.
Execute 1 the statement to get your results.
This is not dynamic SQL and works for stored 1 procs
Declare @ThreePartName varchar (1000)
Declare @DatabaseNameParameter varchar (100)
SET @DatabaseNameParameter = 'MyOtherDB'
SET @ThreePartName = @DatabaseNameParameter + '.Schema.MyOtherSP'
EXEC @ThreePartName @p1, @p2... --Look! No brackets
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.