Pass a database name to a stored procedure and use this variable name in the code

I have been researching and don't see my exact situation. I have a SQL Server stored procedure that references a database with a few lookup tables. The procedure is run from our "main database" and using fully qualified names (Db name, schema, table name) joins to the lookup tables in the lookup database. Every so often we will get an updated version of these lookup values. The new values will be placed into a new database with matching names from prior versions. The database names can change slightly i.e. LookupDB_1 then LookupDB_2. Can I create and pass a variable called LookupDB_2 when I get the update and have LookupDB_2 "point" to the right lookup tables in my coding? There are multiple references to the lookup DB in the code. This would be better than later users doing a find and replace on the lookup database name and altering the procedure.

Example code:

SELECT ColumnNames  
FROM dbo.MainTable m
LEFT JOIN LookupDB_1.dbo.LookupTableName l      -- lookup database name changes 
  ON m.id = l.lookupid

Thanks.

Solution

You could create views of the lookup tables in another database and use them in your stored procedure instead of direct references to the lookup database. Then create a Stored procedure with dynamic SQL to Alter the views when the lookup database changes. This would be simpler to maintain because it would be entirely transparent in any object that referred to the lookups and would also perform better than having to use dynamic SQL throughout your database.