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
– Martin Smith, Commented May 29, 2013 at 11:23
This post provides a detailed solution to this problem: stackoverflow.com/questions/727788/… — JamesT
– JamesT, Commented May 29, 2013 at 11:30
In addition to the solution in the question that J Tolley linked to, SQLCMD scripting variables are intended for exactly this scenario. — Pondlife
– Pondlife, Commented May 29, 2013 at 19:14

Andrey Gordeev · Accepted Answer · 2013-05-29 11:09:06Z

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, 2013 at 11:09

Andrey Gordeev

32.7k16 gold badges143 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

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.