How do you create a database within a stored procedure 1

[bunmiA]

I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


create database @dbname
on
( name = @dbname + 'data',
filename = 'F:\MSSQL\Data\' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:\MSSQL\Data\' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )

select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

end

 

[JayKusch]

declare @iStatus Int
declare @dbname VarChar(50)
declare @SQL_Command VarChar(1000)

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)

SET @DBName = 'JJK'

SET @SQL_Command =

'create database ' + @dbname + '
on
( name = ' + @dbname + '_data' + ',
filename = ' + '''' + 'F:\MSSQL\Data\' + @dbname + '_data.mdf' + '''' + ',
size = 10mb,
filegrowth = 10% )
log on
( name = ' + @dbname + '_log' + ',
filename = ' + '''' + 'F:\MSSQL\Data\' + @dbname + '_log.ldf' + '''' + ',
size = 5mb,
filegrowth = 10% )'

exec (@SQL_Command)


select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)


Thanks

J. Kusch

 

[JayKusch]

Sorry ... get rid of the line ...

SET @DBName = 'JJK'

so you DBName will be used.



Thanks

J. Kusch

 

[bunmiA]

I get the logic.. I need to do the whole create db script within a predefined sql command..

Thanks for your help. I'll try this now.

 

[JayKusch]

Dynamic SQL as we call it! enjoy!

Thanks

J. Kusch