How can I use variables to specify DB names and Servers in SQL Server?

Question

How can I use variables to specify DB names and Servers in SQL Server?

huston.dunlap

SSCommitted

Points: 1551
More actions
October 4, 2009 at 3:51 pm

#136254

I am attempting to do the following.
Let us say I have the following setup: My information is on a server called "BernieServer". On that server is a database called "BernieDB". And then on that DB is a table called "EDDS.Bernie".
I work with a lot of different servers and databases, so I want to be very specific whenever calling out tables.
For example in this case it would be from [BernieServer].[BernieDB].[EDDS].[Bernie]
Now I want to make it easy to change servers in my code, so I would like to use variables to store the names.
For example.
SET @server = 'BernieServer'
SET @DB = 'BernieDB'
and then the command would be from @server.@DB.EDDS.Bernie.
Whenever I try this it says I have an invalid object name. I have tried adding brackets and removing them as well as other possible combinations. Is there something I'm missing in the formatting of my variables. Is this even possible in SQL server?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Well you can't do it quite that way, but you can accomplish the same thing using SYNONYMs.

A synonym is very much like an alias. You could create a synonym called dbo.sBernie in the local DB and it could point at BernieServer.BernieDB.EDDS.Bernie

What this means is you could standardize the objects alias name and then when the DB is built to point at the right place without concern for its actual name.

This will work for SQL 2005 and 2008..

CEWII

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

In case you want to change it programmatically, Can you use dynamic sql, something like this?

Declare @server as nvarchar(50)

Declare @db as nvarchar(50)

Declare @schema as nvarchar(20)

Declare @select as nvarchar(max)

set @server = 'yourserver'

SET @db = 'yourdb'

Set @schema = 'yourschema'

SET @select = 'Select * from ' + @server + '.' + @db + '.' + @schema + '.yourtable'

EXEC sp_executesql @select

---------------------------------------------------------------------------------

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

You can do that. There are some difficulties associated with that. But we would be trying to stay away from dynamic SQL. This also opens the process up to SQL injection.

CEWII

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

Yes, I agree with Elliott. Dynamic sql should be the last resort for you. Thanks.

---------------------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I wouldn't say dynamic sql is a last resort. There may be justifiable reasons for using it. If you are, be sure to test all input to protect against SQL Injection attacks.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I don't know Lynn, I just really try not to use dynamic SQL is there is another reasonable alternative. It certainly has its place..

CEWII