The USE command

Question

Post reply

The USE command

Steve Pettifer

SSC Eights!

Points: 915
More actions
October 8, 2007 at 10:36 am

#182511

Hi all
I was wondering, in the light of SS2K5 now supporting the use of variable in SELECT TOP statements, does it now support the use of variables in USE statements such as :
DECLARE @dbName VARCHAR(10)
SET @dbName = 'myDb'
USE @dbName
I don't suppose it does as I can't get it to work but I wondered if I was missing something (like having to put the variable name in parentheses in the SELECT TOP statement - that caught me out for a bit)?
Cheers
Steve

Viewing 14 posts - 1 through 13 (of 13 total)

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 1

Unfortunately I guess you will have no luck.

The nearest thing you could do is something like this:

DECLARE @myDbName sysname

SET @myDbName = 'Northwind'

EXEC ('USE ' + @myDbName + '; SELECT * FROM sys.objects')

It would help a lot if you could explain a little why you need a dynamic USE statement. Maybe then someone could show you a different solution to your problem.

An example would be to keep the code in one file and use sqlcmd. But that depends as I said on what you are trying to achieve.

http://msdn2.microsoft.com/en-us/library/ms188714.aspx

Best Regards,

Chris Büttner

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 2

You can do this using sqlcmd:

batch file sample:

set servername=SQLInstance,port#

set databasename=sampledb

set tablename=sampletb

sqlcmd -S %servername% -i NameOfScriptFile -o OutputFileName -v db = "%databasename%" tb = "%tablename%"

sqlscript file:

use [$(db)]

select * from $(tb).column

You will need to created a small batch file where you will pass the database name, table etc into variables..

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 3

Hmmm...I figured as much. Sadly, dynamic sql is not something I'm keen on and it wouldn't be suited to the situation I was mulling, nor would the other suggested solution. We have a system which has a forum but for each client using the system, there is a seperate forum database (it's pbpBB2 - I had zero time to get it up and running and had to grab the first reasonably configurable free forum that came to hand). Sadly, there are SPs which would, in an ideal world, be able to communicate with the appropriate db based on an ID so that I don't need to worry about adding a new bit of logic each time we add a client. I'll just have to put some more thought into re-moulding it to a better model.

To be honest it's something I wish I had done differently, but when you have just 4 weeks from your boss landing it on your desk with no warning to finished product and it has to be done before you go on holiday to the 24 Heures Du Mans, mistakes get made!!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Most people recommend against it for many reasons... most of them are good, some are not... but you could put a view in the Master database and that would draw from whatever the "current" database is...

...but I don't see what the big deal about using dynamic SQL is if the proper precautions are taken and it's well written.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 5

Dynamic SQL isn't terribly easy to maintain and of course it has questionable efficiency. I usually find that there is another way if you look hard enough (although it has it's place - making ADSI queries for example) so I will always try pretty much every other way I can think of and use dynamic SQl as a last resort.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

A few 'big deals' that comes to mind:

- opens risk of injection (ie one needs to deal with it properly)

- violating security models/policies (ie is it kosher to grant access directly to basetables?)

Most of the 'baggage' you get when stepping into dynamic SQL is stuff that's related security and design. It's important to go there with both eyes open, aware of the price it costs.

/Kenneth

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Yep... I agree with all of that... if it's for GUI code...

For batches, the proc will probably recompile anyway just because the data will have changed enough to need a recompile. Dynamic does not impinge much overhead on batch code if it's written in a set based fashion. And, remember, if you use something like sp_MSForEachDB, you're using both Dynamic SQL and a Cursor anyway... why not write the code to do it right, instead 😉

Now, does anyone know how to "step through databases" without either putting a view or proc in the Master database or without some form of loop? If so, please post it here. If not, I'm thinking that the warnings about dynamic SQL may be unjustified here...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

What about setting the execution context ?

e.g.

exec some_database_name..sp_some_stored_procedure

This implies 3 things. First you are using a stored procedure. Second, it must reside in the master database. Third it must begin with sp_.

The only issues that I have noticed is that certain niladic functions and do not work as expected.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

For stuff that falls into the 'admin/dba/maint' domain, then dynamic SQL, loops, cursors et al are imo 'fair game'. Usually such code is of a nature that requires loops etc.

For 'user-code' it's another affair. Then the developer should think twice about these things.

I'd say that it's the purpose that decides what's 'good' or 'bad'.

/Kenneth

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

... and that was my point... not all Dynamic SQL is bad nor even indesireable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

Though, in this case it seems like it doesn't fall into the 'dba/admin/maint' category, but in the 'user-code' category....

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Well sure it does, Ken... how else are you going to "step through the databases"? sp_ForEachDB??? That's got dynamic SQL and cursors, too!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 13

Hmmm... Reread Steve's posts where he hints a little, but I realize that we actually don't know exactly what kind of problem he's having... (at least I've missed it)

So, Steve. What are you pondering? And what's the purpose of USE @dbvariable for you?

What kind of 'problem' would that fix for you?

/Kenneth