using use with a @variable

[camy123]

hi i would like to select a database with a variable such as use @variable but use will not allow me to use a variable is there any other way to swictch to a different database

 

[donutman]

Only the dreaded evil called dynamic SQL.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

Nope - you have to use the literal db name. In case you were wondering, you can't even use dynamic SQL as this is executed in a different scope to the calling script:

SELECT db_name(), 1

DECLARE @db sysname

SET @db = 'northwind'

EXEC('USE ' + @db + ' SELECT db_name(), 2')

SELECT db_name(), 3

--James

 

[donutman]

Thank goodness...lest he writes the entire SP in dynamic SQL.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

NB: my script assumes you're not already in the northwind database to begin with!

--James

 

[camy123]

Thanks for the post but my main aim was to jump from one database to another.. ne ideas on this

 

[JamesLean]

You cannot do this dynamically! You can only use fixed USE statements.

--James

 

[camy123]

ive figured it this what i did

EXEC('Use ' + @dbname+ ' create table mytable (col1 int) select * from
mytable')


it works a treat

Thanks again i couldnt have done it without you guys u put me on the right track

thanks

Cam .

 

[dbomrrsm]

James

I cant understand why you say tou cant do this dynamically - your example above produces the result as expected - i.e. the first gives the db you are in the second Northwind and the third the db you are in.

This to me proves you can do it dynamically. Can you explain why you say it can't.

running thios code whilst not in Northwind returns master - the results of select * from categories - then master :

SELECT db_name(), 1

DECLARE @db sysname

SET @db = 'northwind'

EXEC('USE ' + @db + ' SELECT * from categories')

SELECT db_name(), 3

[blue]DBomrrsm[/blue]

 

[JamesLean]

Sorry, re-reading my post I can see why it was confusing!

All I was trying to demonstrate was that you couldn't execute the USE statement dynamically and then do the rest of your (static) SQL in that database. I just included the SELECT in the EXEC to demonstrate that the USE only changed the database context within that scope.

Of course, if you execute everything in one dynamic statement then that's fine, but it's probably not something I would choose to do if I could possibly avoid it...

--James

 

[dbomrrsm]

Thanks James for the clarification

[blue]DBomrrsm[/blue]