USE {database} with a variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

USE {database} with a variable

Author

Topic

kalgat
Starting Member

2 Posts

Posted - 2002-02-20 : 03:13:14

I'm trying to build a sp that uses the USE {database} statement

what type do i give the variable that i am passing to the use statement?

declare @test ??????
set @test = sms
use @test

the use statement fails when any variation of text strings are passed.

I suppose the other question would be is this possible to do?

Apologies in advance if this is a dense question but i'm pulling whats left of my hair out on something that seems so simple!

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 03:21:22

Am not sure what you are trying to do with Use command. but your problem can be solved using Dynamic sql

create procedure aa
as
declare @m as varchar(33)

set @m='use '+@m
exec(@m)

Check on this site's Artilce Search for Dynamic Sql. there are couple of good articles on it.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-20 : 03:32:11

This will work but won't do you any good.
It's the old scope problem
Try

exec ('use <dbnasme> select db_name()')
select db_name()

to see why.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-20 : 03:40:19

The whole idea of stored procedures is that they are meant to reside in the database they are working on.
There are a few ways to execute in another databases context.
You could encapsulate the SP call in dynamic sql probably, but would need a copy of the SP in the database.

exec ('use <dbname> exec mySP')
is the same as
select @sql = 'exec ' + @dbname + '..mySP'
exec (@sql)

or
All the calls within the SP that are database specific are dynamic
select @sql = 'select * from + @dbname + '..tbl tbl, ....'
You can reduce the interaction by using temp tables.

or
use sp_executesql to execute in the databases context (this is the only way to do some things).
select @sql = 'select * from tbl...)
select @sqlcmd = exec @dbname + '..sp_executesql N''' + replace(@sql,'''','''''') + ''''
exec (@sqlcmd)

(something like that).
You will mess up any SP based security by doing this though.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/20/2002 03:43:07

seema
Starting Member

3 Posts

Posted - 2002-04-13 : 02:24:38

I was trying to use the USE statement with a variable.
What I did was Declasre a variable, set a name and then Exec(Use + @variablename),and then tried to create tables , all in a single script , but somehow all tables got created in master instead of the database I specified.
Any solutions?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-15 : 19:53:11

I don't see WHY you would want to do this... If you're creating tables, how dynamic does it need to be?

Regardless, you might be able to do this using 3 or 4-part naming on your tables, along with Dynamic SQL. Something like this...

declare @stmt varchar(2000)
declare @dbname varchar(100)

SET @stmt = 'CREATE TABLE ' + @dbname + '.dbo.TableName (
ID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
.
.
.
)'
EXECT @stmt

That should work, but I wouldn't recommend it. I don't see the need to do this in a dynamic manner... Why do you need to do this?

------------------------
GENERAL-ly speaking...

seema
Starting Member

3 Posts

Posted - 2002-04-16 : 00:56:34

I don't need to create tables dynamicaly. For my app I need to create a test database, for this I require a variable, but some how if i do a Exec('Use' + variablename) the control does not go to that specific database, whereas if I do USE Testdatabase, the control does go to the specific database.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-04-16 : 08:54:32

quote:

I don't need to create tables dynamicaly. For my app I need to create a test database, for this I require a variable, but some how if i do a Exec('Use' + variablename) the control does not go to that specific database, whereas if I do USE Testdatabase, the control does go to the specific database.

Seema,
It does go to the variable database, but only for the scope of the Dynamic statement. When it is done, It reverts to your current db.

Can you chagne the model database to look like the test database you are creating?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-16 : 09:16:20

quote:

...but some how if i do a Exec('Use' + variablename)...

if you do ...


declare @myDB varchar(47)
select @myDB = 'eTheProblem'
exec('Use' + @myDB)

...you exec will resolve to 'UseeTheProblem', which the optimizer will see as an attempt to execute a stored procedure that it can't find and you will get ...

quote:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'UseeTheProblem'.

...

The most important thing on this thread is what nr said about scope.

<O>

seema
Starting Member

3 Posts

Posted - 2002-04-16 : 09:21:50

Page 47, if I do as you suggest then I do not get an error.
But I definitely agree about the scope.
Thanks anyways

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-16 : 13:29:16

So, back to my previous answer, if you use 3-part naming (i.e. DBName.OwnerName.TableName) of your tables that you are creating, it does not matter which database you are "in" when you create them. They will be put into the database that is specifically indentified in the name. But to do this, the entire CREATE TABLE statement has to be in the EXEC statement. I believe this also works for creating stored procedures and other objects. And even for accessing them later, if necessary.

And if you really want to be even more flexible, you could use 4-part naming and make the ServerName dynamic too.

------------------------
GENERAL-ly speaking...

Shawn
Starting Member

9 Posts

Posted - 2002-04-18 : 15:18:42

Continuing on this same vein, I have the same problem, but I do not wish to create tables but users within a database.

I continually build test environments from databases restored from a template. I would like to run a script which builds new users (and other environment specific stuff) for an arbitrary database. Doing this, the best way I can see is to create new users is:

exec sp_grantdbaccess

but this stored procedure only works on the current database. So one needs to use a USE statement to set the current database. Now, I *could* use a dynamic SQL statement to bundle these steps together but I have a whack of other stuff to do and it would be far more understandable and readable if I didn't have to run dynamic SQL all of the time.

So, is there any stored procedure or trick that mimics the USE statement?

Thanks.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-18 : 16:16:26

you could create a wrapper proc that take all the params of sp_grantdbaccess + @dbname. the wrapper proc could do the dynamic sql . . .

<O>

Shawn
Starting Member

9 Posts

Posted - 2002-04-22 : 13:44:57

That's true, and that is exactly what I did. But this means wrapping many of the standard stored procs I use such as sp_helpusers, etc. in another stored proc with the additional dbname parameter and dumping these new sp's somewhere known such as tempdb and running from that context.

I *know* I can do that. I was just wondering if there is some other mechanism, undocumented or not, which allows me to dynamically change database contexts based on a supplied variable holding the desired database name and thus gain access to the native stored procs and then also code my scripts in a more "natural" way.

It's the inquisitive hacker side of me that wants to know. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources