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:26 am
Did you try to include USE command into the dynamic SQL? That should be enough.
select @sql = 'USE db_name create proc newsp as select 1'
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