current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I want change into a database programatically using in code:

DECLARE @SQL nvarchar(2000)
DECLARE @DBName sysname

SET @DBName= 'foo'
SET @SQL = 'USE [' + @DBName + ']'

select @SQL
execute @SQL

When I execute the @SQL I get this error:

Msg 203, Level 16, State 2, Line 9
The name 'USE [foo]' is not a valid identifier.

Is there a way to do this? Thank you in advance.

The goal is to now be in the foo database after this runs.

share|improve this question

closed as off-topic by Kin, Marian, RolandoMySQLDBA, Paul White, Max Vernon Jun 24 at 14:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Kin, Marian, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.
    
Always use QUOTENAME(databaseName) to avoid any surprises. –  Kin Jun 23 at 21:45
    
Related SO question. stackoverflow.com/q/727788/73226 –  Martin Smith Jun 23 at 22:09

1 Answer 1

up vote 1 down vote

I changed 

execute @SQL

to 

exec (@SQL)

and it worked for me. However, the scope of the use is only in the dynamic SQL.

share|improve this answer
    
It works because that's the syntax of execute (when using dynamic sql strings, not procedures). The initial statement would've worked only if @sql was the name of a stored procedure without params. –  Marian Jun 23 at 21:51
    
When I run this I am in the master db, after I run this, I am still in the master DB. Shouldn't I be in the DB called 'Datasets'? –  Max Jun 23 at 21:58
    
@Max No. The scope gets reset when the exec finishes. So you end up back where you started. To use this approach all your remaining code would also need to be appended after the use. –  Martin Smith Jun 23 at 22:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.