Post reply

Copying an SP in code

  • August 18, 2009 at 11:46 am

    #132784

    Hello.

    I'm trying to figure out a way to copy an SP entirely in code.

    Example of use:

    ClientA has a stored procedure as part of their site setup. I would like to create a site for ClientB which has the exact settings as ClientA. However, I want them to have their own copy of this stored procedure, since they may want to has us change it later.

    In effect, I'm trying to take spClientA and make a second copy named spClientB.

    I hope to do it entirely in code so I can enable a user to make the change without help from a DBA.

    Any ideas?

  • August 18, 2009 at 12:25 pm

    #1041082

    try sp_helptext.

    exec sp_helptext 'sp_who'

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • August 18, 2009 at 1:14 pm

    #1041121

    Very good!! I hadn't seen this before. Thank you! The following code is an abstraction of basically what I'll end up doing to accomplish this goal.

    DECLARE @tbl TABLE

    (

    RowText varchar(MAX)

    )

    INSERT INTO @tbl

    exec sp_helptext 'dbo.uspOldName'

    DECLARE @sp-2 varchar(MAX)

    SELECT @sp-2 = ISNULL(@sp, '') + RowText

    FROM @tbl

    SET @sp-2 = REPLACE(@sp, 'uspOldName', 'uspNewName')

    EXEC (@sp)

  • January 12, 2010 at 9:18 am

    #1102513

    this thread just saved me at least an hour of work. thanks SSC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply