USE with local variable

Question

USE with local variable

piershogg

Newbie

Points: 3
More actions
April 22, 2008 at 8:09 am

#93354

Although there are other alternatives, it would be useful if I could use USE database with a local variable in a user stored procedure held in the Master database. I would pass the database name in as a parameter so this is an illustration to check the principle.
eg :
DECLARE @DatabaseName SYSNAME
SET @DatabaseName = '[Number One Of 400 Or So Databases]'
USE @DatabaseName
... Do something with the database
...
Is this possible and, if so, how ?
Thanks,
Piers

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

If the procedure in in the Master database, name it starting with sp_ and it can be called from any database on the server and will run in local database context.

Otherwise, you would need to use dynamic SQL to do what you are saying.

bledu SSChampion Points: 12335 More actions · Answer 2

no i don't think this is possible. you could try to use the USE in a dynamic sql statement.

Everything you can imagine is real.

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 3

piershogg (4/22/2008)
Although there are other alternatives, it would be useful if I could use USE database with a local variable in a user stored procedure held in the Master database. I would pass the database name in as a parameter so this is an illustration to check the principle.
eg :
DECLARE @DatabaseName SYSNAME
SET @DatabaseName = '[Number One Of 400 Or So Databases]'
USE @DatabaseName
... Do something with the database
...
Is this possible and, if so, how ?
Thanks,
Piers

First off, please note that I'm not a fan of dynamic SQL for it's own sake, but run into enough situations where it's necessary to not think it's evil, either. So one thing you could consider is doing the "Do something..." code as dynamic, with a database name prefix to all your objects. So instead of:

set @SQLString = 'select * from dbo.mytable'

do:

set @SQLString = 'select * from ' + quotename(@DatabaseName) + '.dbo.mytable'

That said, do you really need to USE the database in the procedure, or could you simply copy the procedure into the database? There's a couple ways to do that. One would be getting the code from the system tables and compiling it (putting the USE command into the stored proc script itself). Alternatively, I think SSIS has a Transfer Database Objects object in it, which you could use...

Any help?

piershogg Newbie Points: 3 More actions · Answer 4

Thanks Michael, I'll try your suggestion.

I think this is probably not a job for dynamic SQL as the do something I alluded to is quite complex. Although it's not that material, I have between 4 and 5 hundred databases I might need to apply this to.

An outline of the procedure goes something like :

(

CREATE DATABASE

INSERT INTO DATABASE basic information

Use a cursor to read through the database collecting further information from 6 or so moderately complex SELECT statements and stuffing it into about 12 local variables.

Update the database with the information contained in the local variables.

Get next row and repeat until all done.

)

So I'd be grateful for any other hints, tips or suggestions.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 5

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94068

---------------------------------------
elsasoft.org

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 6

This is more optimistic 🙂

http://www.sqlservercentral.com/Forums/Topic475278-338-1.aspx

piershogg Newbie Points: 3 More actions · Answer 7

Thanks all,

In the end I redesigned the stored procedure to use sp_executesql so that I could return values from the dynamic SQL statements. It works pretty well.

Whilst your other suggestions would have worked, this was the final query in a series being invoked by and returning their results to an Excel workbook. So I'd have had to rethink my strategy from scratch.