Can I programmatically clone a stored procedure in tsql script?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Can I programmatically clone a stored...

Can I programmatically clone a stored procedure in tsql script?

Rate Topic

Display Mode

Topic Options

Author

Message

russell.campbell

Posted Wednesday, August 27, 2008 3:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 10:13 AM
Points: 2, Visits: 5

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.

Post #560001

RBarryYoung

Posted Wednesday, August 27, 2008 5:18 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #560021

russell.campbell

Posted Wednesday, September 03, 2008 12:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 10:13 AM
Points: 2, Visits: 5

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.

Post #563303

RBarryYoung

Posted Wednesday, September 03, 2008 1:27 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #563355

« Prev Topic | Next Topic »

Permissions