Can I programmatically clone a stored procedure in tsql script?

  • The setup: db contains a collection of general stored procedures, for instance get_info which takes a set of parameters; if required by business rules for customerX, there may exist a specialized version of a proc, for instance get_info_customerX. As customers are added to the system if a new custom version is needed it requires a developer to script the proc and create a new one custom for the new customer. In the sytem I work on there can be a LOT of these but all of them have defaults.

    What I want to do: I want to build a script that when it runs will, given the indicator that a custom get_info is needed, will programmatically clone get_info to a custom version such that when customer 123 is added to the system and parameters indicate the need the script in question will automagically generate get_info_customer123.

    This is genericized, of course. Is this possible? It would save an awful lot of developer time in my case. Making a script that will duplicate a stored proc but with a new name will greatly enhance the ability for me to build a tool set that will allow someone other than a developer to be involved in initial customer setup in our system.

  • Sure try this:

    USE [TargetDB]

    GO

    Declare @sql NVarchar(MAX)

    Select @sql = Replace(definition, '[dbo].[get_info]', '[dbo].[get_info_customer123]')

    From SourceDB.sys.sql_modules

    Print (@sql) --NOTE: will cut off at TextWidth, which is usually 255-8000 bytes

    EXEC (@sql)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Awesome! Thanks very much for the tip. Not exactly what I wanted but enough to point me to unvisited areas of SQL Server where I was able to figure out exactly what I needed to do.

  • Glad I could help (hmm, it does seem like part of my post is missing...).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi, i'm trying this :

    DECLARE @query nvarchar(max)

    select @query = S.DEFINITION

    from sys.all_sql_modules s

    inner join dbo.sysobjects s2 on s2.id = s.object_id

    where s2.name='PROC_V3N_VENTIL_ACTION'

    exec sp_executesql @query

    It's truncating the @query so i get a synthax error. Please Help !

    Jonathan Bernardez Bernardez
    ___________________________________________________________
    DBD. MCSA SQL Server 2012

  • What error message are you getting?

    Try putting this in your batch and comparing to the original stored procedure:

    SELECT RIGHT(@MYQUERY,50)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi, thanks.

    This is the message :

    Msg 103, Level 15, State 4, Procedure PROC_V3N_VENTIL_ACTION, Line 49

    The identifier that starts with ' SELECT DISTINCT vfd.id_da, vfd.site, pd.nom as pdnom, vfd.fan, i.id_personne, lp.nom,

    lp.prenom,lp.id_personne supp_id, m.l' is too long. Maximum length is 128.

    Inside the stored procedure there is a dynamic query too.

    Jonathan Bernardez Bernardez
    ___________________________________________________________
    DBD. MCSA SQL Server 2012

  • So...you're ok with stepping through the stored procedure to pick up the error, or do you still need help with this?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Running the create procedure inside a new windows works fine, but not through sp_executesql.

    Any idea ?

    Jonathan Bernardez Bernardez
    ___________________________________________________________
    DBD. MCSA SQL Server 2012

  • You must be manipulating the string at some point between getting the definition and executing the dynamic code otherwise the create statement will fail creating a proc with the same name.

    Can you post the actual code that is giving you that error.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

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