-1

I would like to be able to dynamically change database name in stored procedures / views.

Example:

SELECT USER_FNM
FROM MYBASE_01.DBO.USERS

I would like to have MYBASE_01 dynamic, perhaps stored in some variable. Is this possible?

My reason for this is I have many views where database name is included. With deploying, I must change this name in every view. It would be much easier if databases name can be stored only in one variable, so there is no need to change it for all views.

FrenkyB
  • 6,625
  • 14
  • 67
  • 114
  • You can execute a dymanic SQL string where you sub in your name as a string variable. you'd embed your query into an `EXEC()` – EMUEVIL Jul 10 '17 at 16:08
  • For stored procedures you could leverage dynamic sql (although that is a sign that something is not designed ideally), but your views you are stuck. You can't use dynamic sql or variables in views. – Sean Lange Jul 10 '17 at 16:12
  • 1
    Sounds more like a case for SQLCMD variables in your deploy script (`$(database)`). Do you really intend to change the database at *runtime*? If so, it makes much more sense to issue a `USE db` on your connection from client software. If the number of objects you are accessing this way is limited, consider [`CREATE SYNONYM`](https://learn.microsoft.com/sql/t-sql/statements/create-synonym-transact-sql) to alias them transparently. – Jeroen Mostert Jul 10 '17 at 16:18
  • Is it possible to create synonym just for database name? – FrenkyB Jul 10 '17 at 16:29
  • No. You cannot have a single view definition that goes to a runtime-determined database. You'll have to find another solution. – Jeroen Mostert Jul 10 '17 at 17:56

1 Answers1

1

You can execute queries on dynamic tables by using dynamic SQL. As long as the string literal "@DBNAMEVAR" doesn't appear in your query anywhere else, this should work. In order to convert your query to a string, just replace any single quote with two single quotes and then put a single quote and the beginning and the end.

NOTE: this is vulnerable to SQL injection, so sanitize that input or someone could ask your database to do anything.

DECLARE @DBNAMEVAR nvarchar(100); SET @DBNAMEVAR = 'MYBASE_01';
DECLARE @sql nvarchar(max);

SET @sql = REPLACE('SELECT USER_FNM FROM @DBNAMEVAR.DBO.USERS','@DBNAMEVAR',@DBNAMEVAR)
EXEC(@sql)
EMUEVIL
  • 502
  • 3
  • 14