Create DB Name from SELECT

Question

I am trying to create SQL Scripts to move old data to a seperate database. The problem I have right now is that I want to name the Database from an SELECT statement.

use DBName;
declare @release varchar(max)
set @release = concat('Release_',(select MetaRevision from metarevision))
select @release

if db_id(@release) is null create database @release

Sadly this does not work. I get the following error:

Incorrect syntax near '@release'

Is there a way to name a Database from an Select Statement?

exec ('create database ' + quotename(@release)) or something like that. — ta.speot.is
– ta.speot.is, Commented Jun 6, 2018 at 8:27
To explain why: the parameter to create database is a name, not a string/varchar. In other words, create database XYZ is correct, while create database 'XYZ' isn't - and that is why also create database @release is not allowed. — Peter B
– Peter B, Commented Jun 6, 2018 at 8:32
declare @release nvarchar(10) = 'abc'; declare @sql nvarchar(100) = 'create database ' + quotename(@release); exec (@sql); or something like that. — ta.speot.is
– ta.speot.is, Commented Jun 6, 2018 at 8:39

paz0ok · Accepted Answer · 2018-06-06 08:53:21Z

1

Thanks for your help. I found a solution with the information of ta.speot.is and p.campbell.

For the future, this is the solution:

use DBName;
declare @release varchar(14)
set @release = concat('Release_',(select MetaRevision from metarevision))

declare @quoted varchar(16);
set @quoted =  quotename(@release);

if db_id(@release) is null exec ('create database ' + @quoted)

edited Jun 6, 2018 at 8:53

answered Jun 6, 2018 at 8:42

paz0ok

315 bronze badges

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

Comments

SqlKindaGuy · Accepted Answer · 2018-06-06 09:12:59Z

1

You can just do it like this

declare @release varchar(max)
set @release = concat('Release_','TestOst') //Replace TestOst with your metadata
select @release

if db_id(@release) is null 

declare @SQL nvarchar(max)

SET @SQL = 'create database '+quotename(@release)

exec(@SQL)

answered Jun 6, 2018 at 9:12

SqlKindaGuy

3,6172 gold badges17 silver badges33 bronze badges

Collectives™ on Stack Overflow

Create DB Name from SELECT

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related