7

How do I set the database name dynamically in a SQL Server stored procedure?

ErikE
  • 48,881
  • 23
  • 151
  • 196
Srikar Doddi
  • 15,499
  • 15
  • 65
  • 106
  • What you are asking is not entirely clear from your question. Perhaps you should add some more detail to the question. – Raj More Jan 15 '10 at 17:33

3 Answers3

17

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.

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • Is it possible to create synonym just for database name? Example: my database's name is: MYDB_01. Can I create synonym for this? – FrenkyB Jul 10 '17 at 16:15
  • No, it is not possible. Too bad, eh? – ErikE Jul 10 '17 at 16:32
  • Welll. better than nothing. At least no dynamic SQL is needed. But, every object (table, view) must be defined separately. – FrenkyB Jul 10 '17 at 16:51
  • Correct! So it takes dynamic SQL to *create* and *update* the synonyms, but not to refer to them or use them. – ErikE Jul 10 '17 at 16:57
11

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.

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • 6
    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 http://www.sommarskog.se/dynamic_sql.html before using this approach. – Tom H Jan 15 '10 at 17:49
4

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
gbn
  • 422,506
  • 82
  • 585
  • 676