Create a DATABASE using as a Name from a Variable

Question

I would like create a database using value from a variable. Here my script but it does not work. Any ideas?

-- Set Data Base name
DECLARE @DataBaseName char(64);
SET @DataBaseName = 'DbCmsWebsiteTest';

-- Create Data Base
CREATE DATABASE @DataBaseName;
GO

Of course the need to dynamically create a database is usually the sign of a database design flaw. — HLGEM
– HLGEM, Commented Aug 3, 2010 at 18:58
Not if it's used for debugging and local testing, where you want to create multiple dbs for testing purposes. — LarryBud
– LarryBud, Commented Jul 16, 2021 at 11:30

Martin Smith · Accepted Answer · 2010-08-03 16:51:38Z

13

You'd need dynamic SQL for this I think (with appropriate precautions if the name is user supplied)

-- Set Data Base name
DECLARE @DataBaseName sysname;
SET @DataBaseName = 'DbCmsWebsiteTest';

IF (@DataBaseName  LIKE '%[^0-9A-Z]%')
    RAISERROR('Invalid Characters in Name, %s',16,1,@DataBaseName)
ELSE
    BEGIN
    SET @DataBaseName = QUOTENAME(@DataBaseName)
    EXEC('CREATE DATABASE '+ @DataBaseName)
    END

edited Aug 3, 2010 at 16:51

answered Aug 3, 2010 at 15:14

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

2 Comments

Remus Rusanu Over a year ago

But I would not check the valid name rules myself, rather let the CREATE raise. For instance, your check rules out many valid characters like space, _, - etc.

Martin Smith Over a year ago

@Remus - Agreed - I was a bit ambivalent about SQL injection possibilities but I guess QUOTENAME would deal with that anyway.

Corina · Accepted Answer · 2010-08-03 17:59:38Z

2

Actually, the recommended approach is the sp_executesql function.

Here's an example of using it:

DECLARE @SqlCommand NVARCHAR(255), @DatabaseName NVARCHAR(63)
SET @DatabaseName = 'DbName'
SET @SqlCommand = N'CREATE DATABASE ' + @DatabaseName
EXECUTE sp_executesql @SqlCommand

edited Aug 3, 2010 at 17:59

answered Aug 3, 2010 at 15:36

Corina

1,5651 gold badge12 silver badges18 bronze badges

3 Comments

Martin Smith Over a year ago

Gives Incorrect syntax near '@DatabaseName'. You'd need to find some syntax that accepts a parameter for the database name (in which case you wouldn't need dynamic SQL anyway you could just call it directly)

OMG Ponies Over a year ago

In addition to Martin's comment, sp_executesql is SQL Server 2005+ syntax, and ATM we're not aware of what version of SQL Server this is for...

Corina Over a year ago

Got it! I did kinda hurry there :) My idea with sp_executesql was based on the fact that msdn recommends using it when possible. But since its main advantage is when it comes to the execution plans, I guess it's not that important which option is used in this case.

Collectives™ on Stack Overflow

Create a DATABASE using as a Name from a Variable

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related