stored procedure to create new database

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.

re: stored procedure to create new database

"geoff" <cakewalkr7@hotmail.com> wrote in message
news:bf5dd49d.0402271016.b8f3255@posting.google.co m...[color=blue]
> 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.[/color]

This is one way:

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

See CREATE DATABASE in Books Online for full syntax.

Simon

re: stored procedure to create new database

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!

re: stored procedure to create new database

"geoff tyler" <cakewalkr7@hotmail.com> wrote in message
news:403f90f4$1$199$75868355@news.frii.net...[color=blue]
> 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![/color]

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