In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:
> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:
> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johansson
sql
