Create a database with dynamic database name in sq

Question

Create a data base in sql server 2005 using a stored procedure but database name passed as a parameter

Answer 1

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.

Answer 2

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)