2

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.

2
1

I changed

execute @SQL

to

exec (@SQL)

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

3
  • 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 '14 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 '14 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. Jun 23 '14 at 22:05

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