create database with stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create database with stored procedure

Author

Topic

youness_casa18
Starting Member

3 Posts

Posted - 2011-04-26 : 09:57:44

hello to all here
how can I create a database by using stored procedure
this procedure is like this:

USE [master]
GO
alter proc create_new_entreprise (@bd_name varchar(50))
as
begin
if not exists(select database_id from sys.databases where name =@bd_Name)
begin
exec('Create DATABASE'+' '+ @bd_Name)
begin
exec('USE'+' '+ @bd_Name)
create table avion
(
av int primary key,
avmarq varchar(30),
avtype varchar(30),
capacite int,
loc varchar(30)
)
create table pilote
(
pil int primary key,
pilnom varchar(30),
adr varchar(30)
)
create table Vol
(
vol varchar(100) primary key,
av int,
pil int,
vd varchar(30),
va varchar(30),
hd datetime,
ha datetime,
constraint fk_avion_vol foreign key (av) references avion (av),
constraint fk_pilote_vol foreign key (pil) references pilote (pil)
)

end
end
else
print 'la base des données est déjà existante '
end
for more information i use sqlserver 2008

edit: moved to correct forum

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-26 : 09:59:43

you must post this question to "New to SQL Server Programming" or "Transact-SQL (2008)" forums

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-26 : 10:13:02

Vous devez poster votre question dans le forum droit. Profitez
-------------------------
USE [master]
GO
ALTER PROC create_new_entreprise ( @bd_name VARCHAR(50) )
AS
BEGIN
IF NOT EXISTS ( SELECT database_id
FROM sys.databases
WHERE name = @bd_Name )
BEGIN
EXEC ( 'Create DATABASE' + ' ' + @bd_Name
)
BEGIN

EXEC ( 'USE' + ' ' + @bd_Name
)
EXEC ('CREATE TABLE ' +@bd_Name+'..avion
(
av INT PRIMARY KEY,
avmarq VARCHAR(30),
avtype VARCHAR(30),
capacite INT,
loc VARCHAR(30)
)')
EXEC ('CREATE TABLE ' +@bd_Name+'..pilote
(
pil INT PRIMARY KEY,
pilnom VARCHAR(30),
adr VARCHAR(30)
)')
EXEC ('CREATE TABLE ' +@bd_Name+'..Vol
(
vol VARCHAR(100) PRIMARY KEY,
av INT,
pil INT,
vd VARCHAR(30),
va VARCHAR(30),
hd DATETIME,
ha DATETIME,
CONSTRAINT fk_avion_vol FOREIGN KEY ( av ) REFERENCES avion ( av ),
CONSTRAINT fk_pilote_vol FOREIGN KEY ( pil ) REFERENCES pilote ( pil )
)')

END
END
ELSE
PRINT 'la base des données est déjà existante '
END

--------------------------
http://connectsql.blogspot.com/

youness_casa18
Starting Member

3 Posts

Posted - 2011-04-27 : 07:54:41

ok excuse-me

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 08:08:24

duplicate now
But other one locked.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 08:12:03

Looks like this is the one and my post has been deleted from the other thread.

>> EXEC ( 'USE' + ' ' + @bd_Name)
This will work but only for the duration of the statement then it gets reset.
You can create an sp to create the tables and call it as part of the statement
declare @sql varchar(1000)
select @sql = 'USE ' + @bd_Name + char(13)+char(10)+ 'exec s_createtables'
exec (@sql)

or include the create statements in a dynamic sql statement with the use.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 08:12:28

quote:
Originally posted by nigelrivett

duplicate now
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159966 height="1" noshade id="quote">

Yeah, I marked that one and linked back to here. You've just created a Möbius thread.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-27 : 08:19:55

alter proc create_new_entreprise (@bd_name varchar(50))
as
Begin
exec ('use master')
if not exists(select database_id from sys.databases where name =@bd_Name)
Begin
exec('Create DATABASE '+ @bd_name);
exec('USE '+ @bd_Name);--You dont need this row remove this
Begin
exec ('create table '+ @bd_name+'..avion
(
av int primary key,
avmarq varchar(30),
avtype varchar(30),
capacite int,
loc varchar(30)
);')
end
Begin
exec ('create table '+@bd_name+'..pilote
(
pil int primary key,
pilnom varchar(30),
adr varchar(30)
);')
end
Begin
exec('create table '+@bd_name+'..Vol
(
vol varchar(100) primary key,
av int,
pil int,
vd varchar(30),
va varchar(30),
hd datetime,
ha datetime,
constraint fk_avion_vol foreign key (av) references avion (av),
constraint fk_pilote_vol foreign key (pil) references pilote (pil)
);')
End
end
end

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 08:29:23

try this
use mydb -- change this to your database name
exec ('use master select db_name()')
select db_name()

A use statement is only applicable to a batch - as soon as the exec statement completes the context wil revert to the original database.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 08:52:10

>>
exec('USE '+ @bd_Name);
Begin
exec ('create table '+ @bd_name+'..avion
(
>>

Nope - the use statement needs to be in the same dynamic sql string as the create.
Try it with the test code I posted above.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-27 : 08:54:46

Yep that exec('USE '+@db_name); not use in sp

Raghu' S

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 08:58:50

quote:
Originally posted by raghuveer125

Yep that exec('USE '+@db_name); not use in sp

Raghu' S

Sorry - didn't notice you had included the database name with the create statement.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources