How to pass a database name as a parameter in SQL Server

Question

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....

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
– MatBailie, Commented Feb 1, 2012 at 9:32
I assume you've seen this question stackoverflow.com/questions/9093478/… which suggests EXEC. That might be the only way. — Ray
– Ray, Commented Feb 1, 2012 at 9:32
@AK_ ha! My bad. Copy and paste gone wrong. Don't have what I meant to link to anymore. — Ray
– Ray, Commented Feb 1, 2012 at 20:44
Does this answer your question? How can I do something like: USE @databaseName — Wai Ha Lee
– Wai Ha Lee, Commented Jun 24, 2021 at 9:59

Oleg Dok · Accepted Answer · 2012-02-01 09:54:24Z

6

It is impossible to use DB name from variable.

Use Dynamic Querying, even if it is ugly.

answered Feb 1, 2012 at 9:54

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

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

Comments

Oded · Accepted Answer · 2012-02-01 09:32:23Z

2

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

You have several options:

Different DML scripts for each DB
Dynamic SQL

answered Feb 1, 2012 at 9:32

Oded

501k102 gold badges900 silver badges1k bronze badges

Comments

cjk · Accepted Answer · 2012-02-01 09:31:45Z

0

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

answered Feb 1, 2012 at 9:31

cjk

46.6k9 gold badges83 silver badges113 bronze badges

Comments

wallyk · Accepted Answer · 2012-02-01 09:55:31Z

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?

answered Feb 1, 2012 at 9:55

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Collectives™ on Stack Overflow

How to pass a database name as a parameter in SQL Server

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related