Dynamic Sp creation

  • 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

     

    www.sql-library.com[/url]

  • 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'

  • tried that it tells me that alter\create must be the first statement in the batch

    www.sql-library.com[/url]

  • 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?

  • i am automating the setup of replication which has customised replication stored procs. I want the sql which alters the default generated sps to be contained in an sp which i can call from a 'setup' app.

    www.sql-library.com[/url]

  • i found a way

    use db1

    declare

    @statement nvarchar(1000)

    select

    @statement = 'create proc newsp as select 1'

    exec

    db2.dbo.sp_executesql @statement

     

    this will create the sp in the database from which the instance of sp_executesql was taken.

    www.sql-library.com[/url]

  • Use a semi-colon (command terminator) after the USE statement.

    select @sql = 'Use MyOtherDB; create proc newsp as select 1'

     


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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.

  • yep i have tried robert's solution on 2005 it doesnt work. Would be interested to see any other solution but i dont think there is one.

    www.sql-library.com[/url]

  • 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)


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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'

  • 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


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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