Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I programmatically clone a stored procedure in tsql script? Expand / Collapse
Author
Message
Posted Wednesday, August 27, 2008 3:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 27, 2008 5:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
Posted Wednesday, September 3, 2008 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 3, 2008 1:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
Posted Friday, January 10, 2014 4:07 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
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
Post #1529702
Posted Friday, January 10, 2014 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #1529764
Posted Friday, January 10, 2014 7:08 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
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
Post #1529775
Posted Friday, January 10, 2014 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #1529785
Posted Friday, January 10, 2014 7:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
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
Post #1529804
Posted Monday, January 13, 2014 7:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 872, Visits: 2,784
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
Post #1530298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse