Set database name dynamically in SQL Server stored procedure?

by

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.

More Related Contents:

  1. Updating image in VB and SQL Server not working [closed]
  2. If condition in SQL Server 2008
  3. Compare the values of column in SQL Server
  4. Search all tables, all columns for a specific value SQL Server [duplicate]
  5. When/Why to use Cascading in SQL Server?
  6. SQL WHERE.. IN clause multiple columns
  7. SQL Update with row_number()
  8. SQL Server: combining multiple rows into one row
  9. Database scheme, autoincrement
  10. SQL Server: Index columns used in like?
  11. How can I select from list of values in SQL Server
  12. SQL Server tables: what is the difference between @, # and ##?
  13. Recursive sum in tree structure
  14. Getting execute permission to xp_cmdshell
  15. Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility
  16. Why the SQL Server ignore the empty space at the end automatically?
  17. Import / Export database with SQL Server Server Management Studio
  18. Sql Server – connect with windows authentication
  19. SQL Server ON DELETE Trigger
  20. “Cannot open user default database. Login failed.” after installing SQL Server Management Studio Express
  21. SQL Server – Auto-incrementation that allows UPDATE statements
  22. FOR XML PATH(”): Escaping “special” characters
  23. Unable to login to SQL Server + SQL Server Authentication + Error: 18456
  24. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  25. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  26. ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException
  27. SQL Server – How to insert a record and make sure it is unique
  28. “If not exists” fails on SQL CE
  29. Does the `nodes()` method keep the document order?
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment