Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating a Database Dynamically using Store Procedure

Status
Not open for further replies.

krotha

Programmer
Joined
Nov 5, 2000
Messages
116
Location
US
Hi,
I have sql script which creates a database dynamically, I want to convert this script into a stored procedure. Can anybody help me?

Here is my sql script looks like..

set nocount on
set dateformat mdy
USE master
if exists (select * from sysdatabases where name='new_database')
begin
raiserror('Dropping existing 'new_database' database ....',0,1)
DROP database 'new_database'
end
GO
CREATE DATABASE 'new_database' ON DEFAULT = 3
ELSE
CREATE DATABASE 'new_database'
GO

CHECKPOINT

GO

USE new_database
create table a;

How shall I use same script within a stored procedure passing a parameter as 'database_name' to create a new database.

Thanks in adavance
 

You can create the new database and tables in a stored procedure if you use dynamic SQL. You'll have to remove the embedded GO statements. The USE statement doesn't work in a stored procedure.

Example: SP creates a database and 2 tables

CREATE Procedure usp_CreateNewDatabase As

declare @sql nvarchar(4000)

set @sql='Create database dynamo'
Execute (@sql)

Set @sql='Create table dynamo..tbl1 (Id int, col2 char(4))'
Execute (@sql)

Set @sql='Create table dynamo..tbl2 (Id int identity, col2 decimal(12,2), col3 varchar(40))'
Execute (@sql)

GO
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Thanks for your response.
Here you are hard coding the database name 'dynamo'. can i pass a parameter to the procedure and replace the name dynamo. If so what is the syntax for that.

Thanks a lot for your help.
 

Yes.

CREATE Procedure usp_CreateNewDatabase
@dbname nvarchar(60) As

declare @sql nvarchar(4000)

set @sql='Create database ' + @dbname
Execute (@sql)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
This works great.. Thanks a lot for your wonderful help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top