create database then create stored procs in new DB 1

[vasah20]

Hello -
I'm trying to create a new database, then create the necessary stored procedures inside the new database.

I am able to get it to work in Query Analyzer, but I want to be able to convert it into a stored procedure. My problem is that I don't know how I can switch control over to the new database. I tried using (USE DbName) but that gives me errors inside the SP. Any ideas or suggestions?

I'm relatively new at SQL Server, so any advice you could give me would be greatly appreciated.

Thanks in advance,
leo

 

[tlbroadbent]

You can't change databases in a stored proc - use doesn't work.

Clarify for me whether you've already created the stored procedures or want to know how to create them. Or do you refer to executing SP's fom an application after creating them? Terry

http://members.home.net/tlbroadbent/index.htm

 

[vasah20]

I'm creating an Intranet application that creates a database... a template database, if you will.

in this new database, there needs to be some stored procedures. I have the full code for the SPs already.

I know that if worse comes to worse, I can have the application close the connection, then reconnect to the newly created database, then CREATE PROCEDURE that way, but I really wanted to try and isolate all the database creation code into code located on SQL Server.

I hope i cleared it up a little for you...

thanks again for the help.
leo

 

[tlbroadbent]

Here is an excellent solution.

1- Create a system stored procedure in the master database that contains all the code to create your stored procs.

Example:

Create proc sp_CreateStoredProcs As

declare @sql varchar(1024)

set @sql='create proc usp_test1 as print "This is proc 1"'
exec (@sql)
set @sql='create proc usp_test2 as print "This is proc 2"'
exec (@sql)
set @sql='create proc usp_test3 as print "This is proc 3"'
exec (@sql)
go

2- After creating the database, call the system stored procedure with the new database name as a prefix.

Example:

exec NewDBName..sp_CreateStoredProcs

When you execute a system stored proc with a database prefix, the database context is temporarily changed - like issuing a "USE DBNAME" command. The system stored proc will run in the new database, creating the stored procedures (and other objects if you want) in the new DB.

NOTE: A stored procedure name must start with "sp_" and be created in master to become a system SP. : Terry

http://members.home.net/tlbroadbent/index.htm