How do I set the database name dynamically in a SQL Server stored procedure?
-
What you are asking is not entirely clear from your question. Perhaps you should add some more detail to the question.Raj More– Raj More2010-01-15 17:33:22 +00:00Commented Jan 15, 2010 at 17:33
Add a comment
|
3 Answers
Sometimes, the use of SYNONYMs is a good strategy:
CREATE SYNONYM [schema.]name FOR [[[linkedserver.]database.]schema.]name
Then, refer to the object by its synonym in your stored procedure.
Altering where the synonym points IS a matter of dynamic SQL, but then your main stored procedures can be totally dynamic SQL-free. Create a table to manage all the objects you need to reference, and a stored procedure that switches all the desired synonyms to the right context.
This functionality is only available in SQL Server 2005 and up.
This method will NOT be suitable for frequent switching or for situations where different connections need to use different databases. I use it for a database that occasionally moves around between servers (it can run in the prod database or on the replication database and they have different names). After restoring the database to its new home, I run my switcheroo SP on it and everything is working in about 8 seconds.
4 Comments
FrenkyB
Is it possible to create synonym just for database name? Example: my database's name is: MYDB_01. Can I create synonym for this?
ErikE
No, it is not possible. Too bad, eh?
FrenkyB
Welll. better than nothing. At least no dynamic SQL is needed. But, every object (table, view) must be defined separately.
ErikE
Correct! So it takes dynamic SQL to create and update the synonyms, but not to refer to them or use them.
Stored Procedures are database specific. If you want to access data from another database dynamically, you are going to 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 clauses to set the @DatabaseNameParameter to the DB of your liking.
Execute the statement to get your results.
1 Comment
Tom H
Just beware that there are a lot of limitations to this approach. For example, the dynamic SQL is in a completely different scope, so it doesn't have access to table variables, temporary tables, etc. that are in your stored procedure. Also, there are big potential security issues with it. You should check out sommarskog.se/dynamic_sql.html before using this approach.
This is not dynamic SQL and works for stored procs
Declare @ThreePartName varchar (1000)
Declare @DatabaseNameParameter varchar (100)
SET @DatabaseNameParameter = 'MyOtherDB'
SET @ThreePartName = @DatabaseNameParameter + '.Schema.MyOtherSP'
EXEC @ThreePartName @p1, @p2... --Look! No brackets
