August 17, 2011 at 12:44 am
I have created a database using stored procedure.... And a table in that same procedure...
after execution database is created... but I want to create table in that database...but table is getting created under master.... so I used USE database (same database name)...its giving me error that you cant use 'use statement'...
Sagar Sonawane
** Every DBA has his day!!:cool:
August 17, 2011 at 1:05 am
August 17, 2011 at 1:09 am
I have used following procedure....
create procedure Newdb_info
as
begin
create table dbo.Newdb_info.Teachers
( Emp_id int not null primary key, lastname varchar(20) not null ,firstname varchar(20)not null,
Assign_div nvarchar(20), sp_subject varchar(20), Age int)
insert into Teachers values
(101,'patil','krishna', 'fyba','History',45),
(102,'salavi', 'nilima', 'sybcom', 'account',30),
(103,'shinde', 'shekhar', 'tybcom', 'finance',30),
(104,'vanage', 'nita', 'fybsc', 'physics',40),
(105,'sonawane', 'sagar', 'tybsc', 'chemistry',36)
select * from Teachers
end
go
Sagar Sonawane
** Every DBA has his day!!:cool:
August 17, 2011 at 1:11 am
August 17, 2011 at 1:16 am
My apology I have used Newdb_info.dbo.Teachers
it was a typo error.... but still its giving same error...
Sagar Sonawane
** Every DBA has his day!!:cool:
August 17, 2011 at 1:20 am
I repeat :- Please FULLY Qualify the object names
Create procedure Newdb_info
as
begin
create table Newdb_info.dbo.Teachers
( Emp_id int not null primary key, lastname varchar(20) not null ,firstname varchar(20)not null,
Assign_div nvarchar(20), sp_subject varchar(20), Age int)
insert into Newdb_info.dbo.Teachers values
(101,'patil','krishna', 'fyba','History',45),
(102,'salavi', 'nilima', 'sybcom', 'account',30),
(103,'shinde', 'shekhar', 'tybcom', 'finance',30),
(104,'vanage', 'nita', 'fybsc', 'physics',40),
(105,'sonawane', 'sagar', 'tybsc', 'chemistry',36)
select * from Newdb_info.dbo.Teachers values
end
go
BTW this is a very bad example of what to do with a stored procedure
August 17, 2011 at 10:33 am
Yeah why would you create a stored procedure to create a permanent table? You can't run your stored procedure more than once. You probably should be using a temp table instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2011 at 9:59 pm
My question was very simple.....
I am creating a stored procedure to create a database...and in that same stored procedure I want to create a table & insert values in that table.
So in one stored procedure my DB created -> table created -> data inserted...
Sagar Sonawane
** Every DBA has his day!!:cool:
August 18, 2011 at 7:39 am
You can't create a database and reference it in the same stored procedure. You can only use a single batch within a stored procedure and your database will not exist for subsequent ddl commands after creating the database. You will have to create your database in one stored procedure and the ddl for that database in a second procedure. You can however call the second stored proc from the first one after you create the database. This seems pretty odd to use a proc for this but I can certainly see some cases where it makes sense. Something like this should work.
create procedure Newdb_info
as begin
CREATE TABLE Sales.[dbo].[Product]
(
[ProductId] [uniqueidentifier] DEFAULT NEWID() NOT NULL,
[ProductName] [nchar](50) NULL,
[ProductDescription] [nchar](3000) NULL,
[ProductPrice] MONEY NULL
) ON [PRIMARY]
INSERT INTO [Sales].[dbo].[Product]
([ProductName] ,[ProductDescription],[ProductPrice])
VALUES ('Product ', 'Description ', 100)
end
Notice the fully qualified object names. Now you will need to proc to create the db.
create procedure Create_NewDB
as begin
CREATE DATABASE [Sales] ON PRIMARY
( NAME = N'Sales', FILENAME = N'c:\_temp\Sales.mdf' ,
SIZE = 10MB , MAXSIZE = 8GB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'Sales_log', FILENAME = N'c:\_temp\Sales_log.ldf' ,
SIZE = 10MB , MAXSIZE = 2GB , FILEGROWTH = 10%)
exec Newdb_info
end
Almost there...
exec Create_NewDB
Proof is in the pudding.
select * from sales.dbo.product
That get you in the right direction?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 18, 2011 at 8:35 am
Sean Lange (8/18/2011)
You can't create a database and reference it in the same stored procedure. You can only use a single batch within a stored procedure and your database will not exist for subsequent ddl commands after creating the database. You will have to create your database in one stored procedure and the ddl for that database in a second procedure. You can however call the second stored proc from the first one after you create the database. This seems pretty odd to use a proc for this but I can certainly see some cases where it makes sense. Something like this should work....
Actually, you can, but you have to go out-of-scope to do so.
create proc dbo.MyProc
as
exec('create database MyNewDB');
exec('create table MyNewDB.dbo.MyNewTable (Col1 int)');
exec('insert into MyNewDB.dbo.MyNewTable (Col1) values (1)');
The exec commands count as separate batches, in terms of execution scope, so they will work.
HOWEVER, it would be completely foolish to do this.
First, there's no reason to use a proc to create a database. Procs are designed for repeated work, not for single-shot actions. Unless you are routinely creating and dropping the same database, it's going to be easier, more effective, and more useful, to create the database through a script, not a proc.
Second, it's going to be easier to write this as a script. Dynamic SQL of this sort is a pain to write, especially as you get into nested single-quotes for string values. Lots of extra work for absolutely zero gain.
Third, you really don't want to open this kind of can of worms security-wise. If a proc has to be executed in such a way that dynamic SQL inside it can modify server-level objects (like creating a database), you've just created a hacker's wet dream on your server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2011 at 8:52 am
This meets the requirements, but why you'd need something like this is beyond me. Dynamic sql, use at your own risk...
CREATE PROCEDURE USP_CreateDatabase (
@DBName varchar(50)
) AS
BEGIN
Declare @sql varchar(8000)
Set @sql = 'Create Database ' + @DBName + ' ON Primary ( NAME = N'''
+ @DBName + ''', ' + ' FILENAME = N''c:\' + @DBName + '.mdf'')'
+ ' LOG ON ( NAME = N''' + @DBName + '_log'', FILENAME = N''c:\' + @DBName + '_log.ldf'')'
exec(@sql)
Set @sql = 'Create Table ' + @DBName + '.dbo.TableName ('
+ 'Id Int IDENTITY(1,1) NOT NULL, '
+ 'Description varchar(50) NOT NULL)'
exec(@sql)
Set @sql = 'Insert Into ' + @DBName + '.dbo.TableName (Description) '
+ 'Values(''hello there'')'
exec(@sql)
END
August 18, 2011 at 11:29 pm
nellisjp (8/18/2011)
but why you'd need something like this is beyond me
I've had to do such a thing many times even if I don't necessarily agree with it. It's a "deployment" proc from a "master system database" wich may do things like create a new database fore each day or month or client or...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2011 at 6:42 am
Jeff Moden (8/18/2011)
nellisjp (8/18/2011)
but why you'd need something like this is beyond meI've had to do such a thing many times even if I don't necessarily agree with it. It's a "deployment" proc from a "master system database" wich may do things like create a new database fore each day or month or client or...
It's easier to create a script and run it in a .NET CLR object, with some variables or inputs to manage the database name and all that. Less nesting of single quotes and things like that, and better error handling.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2011 at 6:11 pm
GSquared (8/19/2011)
Jeff Moden (8/18/2011)
nellisjp (8/18/2011)
but why you'd need something like this is beyond meI've had to do such a thing many times even if I don't necessarily agree with it. It's a "deployment" proc from a "master system database" wich may do things like create a new database fore each day or month or client or...
It's easier to create a script and run it in a .NET CLR object, with some variables or inputs to manage the database name and all that. Less nesting of single quotes and things like that, and better error handling.
Agreed. If you have someone on your team who knows how to write some .NET CLR code...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2011 at 8:53 am
nellisjp (8/18/2011)
This meets the requirements, but why you'd need something like this is beyond me. Dynamic sql, use at your own risk...
CREATE PROCEDURE USP_CreateDatabase (
@DBName varchar(50)
) AS
BEGIN
Declare @sql varchar(8000)
Set @sql = 'Create Database ' + @DBName + ' ON Primary ( NAME = N'''
+ @DBName + ''', ' + ' FILENAME = N''c:\' + @DBName + '.mdf'')'
+ ' LOG ON ( NAME = N''' + @DBName + '_log'', FILENAME = N''c:\' + @DBName + '_log.ldf'')'
exec(@sql)
Set @sql = 'Create Table ' + @DBName + '.dbo.TableName ('
+ 'Id Int IDENTITY(1,1) NOT NULL, '
+ 'Description varchar(50) NOT NULL)'
exec(@sql)
Set @sql = 'Insert Into ' + @DBName + '.dbo.TableName (Description) '
+ 'Values(''hello there'')'
exec(@sql)
END
Watch out for Little Bobby Tables[/url] (aka SQL injection). At least put @DBName inside the QUOTENAME function.
USP_CreateDatabase ';exec sp_msforeachdb ''drop database ?'''
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply