130

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?

1

4 Answers 4

143

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)
4
  • 2
    +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, 2011 at 18:17
  • 2
    Need to be careful with escaping, though.
    – usr
    May 29, 2013 at 11:57
  • 4
    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) Nov 14, 2013 at 11:03
  • 1
    Doesn't work if I want to CREATE SCHEMA in other database, using USE {DBNAME}. Schema creates in wrong database ;/
    – Bomberlt
    Jun 26, 2015 at 7:50
106

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.

3
  • 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, 2015 at 23:26
  • 2
    @syclee A TSQL variable? No the sqlcmd substitutions are carried out before the script is even sent to the server. Apr 14, 2015 at 7:22
  • @MartinSmith Hey i want the db name as a OUTPUT command. So how can i have it using SQLCMD.? Dec 29, 2015 at 8:29
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.

5
  • EXEC looks for a stored procedure. In this case EXECUTE is needed.
    – Bob Blogge
    Jul 18, 2013 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, 2013 at 13:36
  • 2
    Don't do this in production... Ever. Sep 15, 2017 at 18:00
  • @MagicOctopusUrn vary old post and very comments but I am curious know why not? shouldn't it be requirement specific??
    – Harsh
    Nov 9, 2018 at 5:52
  • @MagicOctopusUrn Is that because of potential for injection? May 23, 2019 at 5:12
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);

Not the answer you're looking for? Browse other questions tagged or ask your own question.