CODEWITHSUNDEEP
sql-server
Cotten
Cotten

Reputation: 9077

Database name as variable

I have a database with stored procedures which selects data from another database

select * from Users in AnotherDatabase.dbo.Users

I have a couple of versions of the other database, i.e.

AnotherDatabaseProduction, AnotherDatabaseDevelopment, AnotherDatabaseStage

I remember a while back that I created some kind of global variable like AnotherDatabase_Pointer which I could set by running some stored procedure like SetAnotherDatabaseToStage:

AnotherDatabase_Pointer = 'AnotherDatabaseStage' //pseudo-code

Which I could then use in my stored procedures.

But I cannot seem to remember how it was done. All I can find is how to do this with string replacing/concatenating: variable database name :(

Is this possible? MS SQL.

Upvotes: 0

Views: 497

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280643

You are probably thinking of a Synonym.

CREATE SYNONYM dbo.whatever FOR dbname.dbo.procedure_name;

Now, if you want to change it to some other database, you just drop and re-create the synonym:

DROP SYNONYM dbo.whatever;
GO
CREATE SYNONYM dbo.whatever FOR other_dbname.dbo.procedure_name;

Upvotes: 5

Related Questions