3
USE master
GO

DECLARE @DbName nvarchar(MAX)
SET @DbName = N'DataBase'

ALTER DATABASE @DbName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE @DbName SET OFFLINE WITH NO_WAIT
GO

ALTER DATABASE @DbName SET ONLINE
GO

ALTER DATABASE @DbName
SET MULTI_USER
GO

I know i can use EXEC but it's a bit ugly....

AK_
  • 7,981
  • 7
  • 46
  • 78
  • 1
    You can't parameterise object references in most DML/DDL. You do indeed need EXEC / sp_executesql, and to build up some dynamic queries. – MatBailie Feb 01 '12 at 09:32
  • 1
    I assume you've seen this question http://stackoverflow.com/questions/9093478/how-to-pass-a-database-name-as-a-parameter-in-sql-server which suggests EXEC. That might be the only way. – Ray Feb 01 '12 at 09:32
  • 2
    @Ray you linked to my own question :-P – AK_ Feb 01 '12 at 19:49
  • @AK_ ha! My bad. Copy and paste gone wrong. Don't have what I meant to link to anymore. – Ray Feb 01 '12 at 20:44
  • Does this answer your question? [How can I do something like: USE @databaseName](https://stackoverflow.com/questions/3788566/how-can-i-do-something-like-use-databasename) – Wai Ha Lee Jun 24 '21 at 09:59

4 Answers4

6

It is impossible to use DB name from variable.

Use Dynamic Querying, even if it is ugly.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
2

You can't use the database name in a variable.

You have several options:

Oded
  • 489,969
  • 99
  • 883
  • 1,009
0

Firstly, you can't parameterise DDL statements like this. Secondly, GO is a batch terminator and parameters won't be available after this.

cjk
  • 45,739
  • 9
  • 81
  • 112
0

I don't recall if MSSqlServer allows the same flexibility as Oracle and MySQL, but in those you can set the default database for each connection. If the queries and statements do not specify a database (use (dbname)), it uses the default. Perhaps that is sufficient parametrization for your purposes?

wallyk
  • 56,922
  • 16
  • 83
  • 148