August 22, 2002 at 10:17 am
All - please forgive if there is a very obvious answer to this question as I am just cutting my teeth will all this stuff. I am trying to "script" the creation of a database, based on parameters that I would be passing in. (ultimately I'd like to have this in a stored procedure or a web page to feed the parameters)
This is the "code" that I have so far.
CREATE PROCEDURE usp_create_db (
@db_name varchar(50),
@db_data_size int,
@db_log_size int,
@db_admin_name varchar(50),
@db_user_name varchar(50)
)
AS
CREATE DATABASE [@db_name]
ON (
NAME = N@db_name+'_Data',
FILENAME = N'f:\sql\data\'+@db_name+'_Data.MDF' ,
SIZE = @db_data_size,
FILEGROWTH = 10%)
LOG ON (
NAME = N@db_name+'_Log',
FILENAME = N'G:\sql\log\'+@db_name+'_Log.LDF' ,
SIZE = @db_log_size,
FILEGROWTH = 10%)
GO
There is more to be done with adding of users and such ... but until I get past this, I'm stuck.
Thanks in advance for your help!
August 22, 2002 at 10:34 am
Not sure exactly what your problem is, but if I was to write a procedure similar to what you want to do it would look something like this. Hope this helps you get over your problem:
drop procedure usp_create_db
go
CREATE PROCEDURE usp_create_db (
@db_name varchar(50),
@db_data_size int,
@db_log_size int,
@db_admin_name varchar(50),
@db_user_name varchar(50)
)
AS
declare @CMD varchar(2000)
set @CMD = 'CREATE DATABASE [' + @db_name + '] ' +
' ON ( ' +
'NAME = N''' + @db_name+ '_Data'',' +
'FILENAME = N''f:\sql\data\' + @db_name+ '_Data.MDF'',' +
'SIZE = ' + cast(@db_data_size as char) + ',' +
'FILEGROWTH = 10%) ' +
'LOG ON ( ' +
'NAME = N''' + @db_name + '_Log'',' +
'FILENAME = N''G:\sql\log\' + @db_name + '_Log.LDF'',' +
'SIZE = ' + cast(@db_log_size as char) + ',' +
'FILEGROWTH = 10%) '
print @CMD
exec @CMD
GO
-------------------------
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 23, 2002 at 4:54 am
Your problem is sections of the code like this.
CREATE DATABASE [@db_name]
where a vriable cannot be used. However in GAL0303s version, dynamic SQL is being used so variables can be substituted for those sections as the code is not compiled until execution on the SQL string.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply