How to use a variable for the database name in T-SQL?

Question

I use the database name in several places in my script, and I want to be able to quickly change it, so I'm looking for something like this:

DECLARE @DBNAME VARCHAR(50)
SET @DBNAME = 'TEST'

CREATE DATABASE @DBNAME
GO
ALTER DATABASE @DBNAME SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE @DBNAME SET RECOVERY SIMPLE 
GO

But it doesn't work. So what's the correct way to write this code?

Strongly related: [How can I do something like: USE @databaseName](https://stackoverflow.com/q/3788566/1364007) — Wai Ha Lee, Jun 24 '21 at 10:02

John Saunders · Accepted Answer · 2016-12-15T01:27:01.763

Put the entire script into a template string, with {SERVERNAME} placeholders. Then edit the string using:

SET @SQL_SCRIPT = REPLACE(@TEMPLATE, '{SERVERNAME}', @DBNAME)

and then run it with

EXECUTE (@SQL_SCRIPT)

It's hard to believe that, in the course of three years, nobody noticed that my code doesn't work!

You can't EXEC multiple batches. GO is a batch separator, not a T-SQL statement. It's necessary to build three separate strings, and then to EXEC each one after substitution.

I suppose one could do something "clever" by breaking the single template string into multiple rows by splitting on GO; I've done that in ADO.NET code.

And where did I get the word "SERVERNAME" from?

Here's some code that I just tested (and which works):

DECLARE @DBNAME VARCHAR(255)
SET @DBNAME = 'TestDB'

DECLARE @CREATE_TEMPLATE VARCHAR(MAX)
DECLARE @COMPAT_TEMPLATE VARCHAR(MAX)
DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX)

SET @CREATE_TEMPLATE = 'CREATE DATABASE {DBNAME}'
SET @COMPAT_TEMPLATE='ALTER DATABASE {DBNAME} SET COMPATIBILITY_LEVEL = 90'
SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE'

DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@COMPAT_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

+1 Very nice approach... You just saved me from a ton of work, thx... Though it should be EXECUTE(@SQL_SCRIPT), or at least that is what worked for me. — reSPAWNed, Sep 14 '11 at 18:17
`SYSNAME` would be a more appropriate datatype than `VARCHAR(255)` also should use `QUOTENAME` to deal with all possible database names (and possibly to prevent SQL injection depending on source of the name) — Martin Smith, Nov 14 '13 at 11:03
Doesn't work if I want to `CREATE SCHEMA` in other database, using `USE {DBNAME}`. Schema creates in wrong database ;/ — Bomberlt, Jun 26 '15 at 07:50

score 109 · Answer 2 · edited Apr 06 '15 at 10:06

109

You can also use sqlcmd mode for this (enable this on the "Query" menu in Management Studio).

:setvar dbname "TEST" 

CREATE DATABASE $(dbname)
GO
ALTER DATABASE $(dbname) SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE $(dbname) SET RECOVERY SIMPLE 
GO

EDIT:

Check this MSDN article to set parameters via the SQLCMD tool.

edited Apr 06 '15 at 10:06

Jeremy Thompson

61,933
36
195
321

answered Feb 11 '12 at 15:46

Martin Smith

438,706
87
741
845

1

how can this method use already declared variables? Instead of "TEST" can you add a @dbName? I tried and didn't work – syclee Apr 13 '15 at 23:26
2

@syclee A TSQL variable? No the sqlcmd substitutions are carried out before the script is even sent to the server. – Martin Smith Apr 14 '15 at 07:22
@MartinSmith Hey i want the db name as a OUTPUT command. So how can i have it using SQLCMD.? – Kunal Kakkad Dec 29 '15 at 08:29

score 13 · Answer 3 · edited Jul 31 '15 at 14:06

13

Unfortunately you can't declare database names with a variable in that format.

For what you're trying to accomplish, you're going to need to wrap your statements within an EXEC() statement. So you'd have something like:

DECLARE @Sql varchar(max) ='CREATE DATABASE ' + @DBNAME

Then call

EXECUTE(@Sql) or sp_executesql(@Sql)

to execute the sql string.

edited Jul 31 '15 at 14:06

Marvin Dickhaus

785
12
27

answered Apr 07 '09 at 22:10

Dillie-O

29,277
14
101
140

`EXEC` looks for a stored procedure. In this case `EXECUTE` is needed. – Bob Blogge Jul 18 '13 at 18:42
2

Actually I need to apologize. It turns out `EXEC` and `EXECUTE` are the same. I made the statement after failing with `EXEC` and succeeding with `EXECUTE`. Though obviously my real problem was unrelated to either. – Bob Blogge Jul 19 '13 at 13:36
2

Don't do this in production... Ever. – Magic Octopus Urn Sep 15 '17 at 18:00
@MagicOctopusUrn vary old post and very comments but I am curious know why not? shouldn't it be requirement specific?? – Harsh Shankar Nov 09 '18 at 05:52
@MagicOctopusUrn Is that because of potential for injection? – Isaac Reefman May 23 '19 at 05:12

score 5 · Answer 4 · edited Feb 11 '12 at 15:47

5

You cannot use a variable in a create table statement. The best thing I can suggest is to write the entire query as a string and exec that.

Try something like this:

declare @query varchar(max);
set @query = 'create database TEST...';

exec (@query);

edited Feb 11 '12 at 15:47

Martin Smith

438,706
87
741
845

answered Apr 07 '09 at 22:10

Andrew Hare

344,730
71
640
635

How to use a variable for the database name in T-SQL?

4 Answers4

Linked

Related