CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Marc
Marc

Reputation: 9282

Accessing another database with dynamic name in SQL Server

There are two databases in SQL Server 2005: One called "A" and another one called "A_2". "A" is a variable name to be entered by the user, the "_2" prefix for the second database is always known. (So databases could be "MyDB" and "MyDB_2", etc)

How to access the other database from within a stored procedure without knowing the actual name and without using the 'exec' statement?

Upvotes: 4

Views: 9225

Answers (3)

Eric Kassan
Eric Kassan

Reputation:

What I have done is create a view for each table I wanted to access (presumably the schemas are the same), and then my subsequent code just referenced the view(s). For example

if object_id('view_Table1') is not null
    drop view view_Table1

dim @cmd nvarchar(max)

set @cmd = 'create view view_Table1 as select * from ' + @DbName + '.dbo.Table1'

exec sp_executesql @cmd

select WhateverColumn from view_Table1

Upvotes: 0

Tomalak
Tomalak

Reputation: 338128

You can try to use a new SQL Server 2005 feature, called synonyms.

You would need to CREATE SYNONYM, then compile and save the stored procedure using the synonym. This would leave you with the possibility to change the synonym "on the fly" later on. Obviously, it would still be necessary to run the CREATE SYNONYM code as dynamic SQL.

To run CREATE SYNONYM, the user must be granted the respective permission, or have ALTER SCHEMA permission. Be sure to read more about synonyms before you go that route, there are a few strings attached.

Upvotes: 7

Biri
Biri

Reputation: 7181

I don't think that it is possible.

The name is a variable and you cannot use variables as database names.

So the only way is to put the whole command to a string and exec it, which you would like to avoid.

What is the purpose of the whole thing? What happens if you name your databases on your logic, but somewhere store the link between your logic and the name entered by the user?

Upvotes: 0

Related Questions