Create a database with dynamic database name in sql server 2005

Create a开发者_StackOverflow社区 data base in sql server 2005 using a stored procedure but database name passed as a parameter

Below query block might be helpful.

DECLARE @Query VARCHAR(MAX)=''
DECLARE @DbName VARCHAR(400) = 'Db1'
DECLARE @DbFilePath VARCHAR(400) = 'E:\Database\'
SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON  PRIMARY '
SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
SET @Query = @Query + ' LOG ON '
SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
print @query
exec(@query)

As bitxwise has said, you will need dynamic SQL

create proc createdb @dbname sysname
as
declare @sql nvarchar(max)
set @sql = 'create database ' + QUOTENAME(@dbname)
exec (@sql)

That's just the most basic CREATE DATABASE statement. Normally in a production environment, you wouldn't want to have such a proc anyway, and even if you do - you would specify growth, initial size, location etc.

But here's the reference: http://msdn.microsoft.com/en-us/library/ms176061(v=SQL.90).aspx.

Use EXEC sp_executesql:

DECLARE @Query NVARCHAR(MAX)
set @Query=''
DECLARE @DbName NVARCHAR(max) = 'Db1'
DECLARE @DbFilePath NVARCHAR(max) = N'E:\Database\'
SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON  PRIMARY '
SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' ,  SIZE = 12288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
SET @Query = @Query + ' LOG ON '
SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 32448KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
print @query
EXEC sp_executesql @query