SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I programmatically clone a stored procedure in tsql script?


Can I programmatically clone a stored procedure in tsql script?

Author
Message
russell.campbell
russell.campbell
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55198 Visits: 9518
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."
russell.campbell
russell.campbell
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55198 Visits: 9518
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."
jonysuise
jonysuise
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 166
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
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65884 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
jonysuise
jonysuise
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 166
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
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65884 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
jonysuise
jonysuise
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 166
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
Sean Pearce
Sean Pearce
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6268 Visits: 3436
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

@SeanPearceSQL

About Me
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search