How to use variable for 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?

Thanks

Answer 1

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;

Answer 2

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:

SELECT @Sql ='CREATE DATABASE ' + @DBNAME

Then call

EXEC(@Sql) or sp_executesql(@Sql)

to execute the sql string.

Answer 3

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