Dynamic use of USE command

Question

In TSQL I frequently use the use (excuse me for the wordplay):

use choosenOne

set the choosenOne database as active database. Suppose now something like:

DECLARE @NewDB varchar(255) 
SET @NewDB = 'choosenOne'

I want dinamically compose a valid use command, but I have no luck, since these codes aren't working:

use @NewDB          -- Incorrect syntax near '@NewDB'.
EXEC('use '+@NewDB) -- No runtime error but no change of db is performed

What is the right way?

A change of db is actually performed. It just is only valid for the child scope and gets changed back when the scope exits. — Martin Smith, May 29 '13 at 11:23
This post provides a detailed solution to this problem: http://stackoverflow.com/questions/727788/how-to-use-variable-for-database-name-in-t-sql — JamesT, May 29 '13 at 11:30
In addition to the solution in the question that J Tolley linked to, [SQLCMD scripting variables](http://msdn.microsoft.com/en-us/library/ms188714.aspx) are intended for exactly this scenario. — Pondlife, May 29 '13 at 19:14

score 1 · Answer 1 · answered May 29 '13 at 11:09

1

You should put your query into EXEC command after 'use '+@NewDB:

DECLARE @NewDB varchar(255) 
SET @NewDB = 'choosenOne'
EXEC('use '+@NewDB + '
 ...............')

answered May 29 '13 at 11:09

Andrey Gordeev

I was looking for a more elegant solution. Too many quotes in the real query and really error prone and unreadable solution in this case. – BAD_SEED May 29 '13 at 11:19

1 Answers1