November 1, 2006 at 5:12 am
I need a way to dynamically create Stored procs using a stored proc located in a diffferent db from where the sps are created.
so how do i rewrite this so that sp newsp is created on a different db.
create proc test
as
declare @sql varchar(max)
select @sql = 'create proc newsp as select 1'
exec (@sql)
go
Many Thanks,
Jules
November 1, 2006 at 5:27 am
November 1, 2006 at 5:36 am
Oh... right, I forgot about that. Well, then no, I don't know how to create procedure in another DB using dynamic SQL - but I wouldn't want to do it anyway. Are you sure that creating procedures with dynamic SQL is the solution you need? Isn't there any other possibility?
November 1, 2006 at 5:48 am
November 1, 2006 at 6:15 am
November 1, 2006 at 11:18 pm
Use a semi-colon (command terminator) after the USE statement.
select @sql = 'Use MyOtherDB; create proc newsp as select 1'
November 2, 2006 at 12:45 am
No, ; and GO will not work in dynamic SQL - at least not in SQL Server 2000. The only way is to change database before you start dynamic SQL, as Jules wrote in his last post.
November 2, 2006 at 3:19 am
November 2, 2006 at 12:09 pm
First of all, a semi-colon command terminator DOES indeed work in dynamic SQL. I use it all the time. For example:
Use
master
Declare
@sql nvarchar(100)
Set
@sql = 'Use msdb; Select db_name() As DBName'
Exec
sp_executesql @sql
DBName
----------------------------
msdb
(1 row(s) affected)
However, it does not work before a Create Procedure statement, whether dynamic or ad hoc.
Nesting your dynamic SQL statements will work though. Like so:
Use
msdb
Declare
@sql nvarchar(100)
Set
@sql = 'Use master; Exec sp_executesql N''Create Procedure dbo.USP_TestProc As Select db_name()'''
Exec
sp_executesql @sql
Exec
master.dbo.USP_TestProc
Use
master
Drop
Procedure dbo.USP_TestProc
DBName
----------------------------
master
(1 row(s) affected)
November 3, 2006 at 12:57 am
Sorry for inaccurate formulation, Robert; of course you're right that semicolon can be used. What I meant is that it will not help in this particular case because of CREATE PROCEDURE.
BTW, you don't have to use it - result is the same even without semicolon.
Set @sql = 'USE msdb SELECT db_name() As DBName'
November 3, 2006 at 3:09 am
No, but there are cases where it wouldn't work without a semi-colon. For example, I have a stored procedure that performs a maintenance task with many steps. The procedure basically calls a series of other procedures, but at one point in the process, it needs to disable a database level trigger for a procedure to run and then re-enable it to run after the procedure finishes. It looks something like this:
Exec MyDatabase.dbo.Procedure1
Exec MyDatabase.dbo.Procedure2
Exec MyDatabase.dbo.Procedure3
Exec MyDatabase.dbo.Procedure4
Exec MyDatabase.dbo.Procedure5;
Disable Trigger TR_DDL_TableEvents On Database;
Exec MyDatabase.dbo.Procedure6;
Enable Trigger TR_DDL_TableEvents On Database;
Exec MyDatabase.dbo.Procedure7
Exec MyDatabase.dbo.Procedure8
November 6, 2006 at 11:05 pm
create proc temp_proc as
declare @db varchar(50);
declare @CProc varchar(500);
declare @DProc varchar(500);
set @CProc=' create procedure _proc_name as select name from sysobjects'
set @DProc = 'drop procedure _proc_name'
set @db='tempdb '
exec('use ' + @db + ';' + 'exec(''' + @DProc + ''')')
exec('use ' + @db + ';' + 'exec(''' + @CProc + ''')')
--exec temp_proc
Create parameters for database(where u wan 2 create) and procedure name.
Hope it will be useful
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy