stored procedure to create new database

[geoff]

Is there a stored procedure installed by sql server 2000 that I can
call and just pass in the name of a new database and have it create
the database for me? If not, how do I do it in sql? Thanks.

[Simon Hayes]

"geoff" <ca********@hotmail.com> wrote in message
news:bf*************************@posting.google.co m...

Is there a stored procedure installed by sql server 2000 that I can
call and just pass in the name of a new database and have it create
the database for me? If not, how do I do it in sql? Thanks.

This is one way:

declare @newdb sysname
set @newdb = 'NewDatabase'
exec('create database ' + @newdb)

See CREATE DATABASE in Books Online for full syntax.

Simon

[geoff tyler]

Thanks Simon, actually, I just remembered BOL after I made the post. I
found the syntax for CREATE DATABASE "DBName". I ran it in query
analyzer to test it and it worked fine. So I figured I'd make a stored
procedure out of it, but it's bombing on that line saying there's an
incorrect syntax. Do you see a problem with my code here? Thanks
again.

CREATE PROCEDURE CreateNewClientDatabase

@pDBName varchar (128)

AS

if not exists(select dbid from master.sysdatabases where name =
@pDBName)
CREATE DATABASE @pDBName
else
raiserror("Database already exists.",3)

if @@error = 0
return 0
else
return -1

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"geoff tyler" <ca********@hotmail.com> wrote in message
news:40*********************@news.frii.net...

Thanks Simon, actually, I just remembered BOL after I made the post. I
found the syntax for CREATE DATABASE "DBName". I ran it in query
analyzer to test it and it worked fine. So I figured I'd make a stored
procedure out of it, but it's bombing on that line saying there's an
incorrect syntax. Do you see a problem with my code here? Thanks
again.

CREATE PROCEDURE CreateNewClientDatabase

@pDBName varchar (128)

AS

if not exists(select dbid from master.sysdatabases where name =
@pDBName)
CREATE DATABASE @pDBName
else
raiserror("Database already exists.",3)

if @@error = 0
return 0
else
return -1

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If you check the syntax description for CREATE DATABASE, you'll see that it
doesn't accept variables - that was why my example used EXEC() to build a
string and then execute it. A couple of other points:

* master..sysdatabases - note the additional full stop required (see "Using
Identifiers as Object Names")
* Avoid using double quotes (see SET QUOTED_IDENTIFIER)
* Your RAISERROR syntax is incorrect (no value for state)
* Checking @@ERROR at that point doesn't do much - see this link for more
details on how to use @@ERROR correctly:

http://www.sommarskog.se/error-handling-II.html

Simon