How to Copy SPROC and Rename?

Question

How to Copy SPROC and Rename?

quinn.jay

SSCertifiable

Points: 5505
More actions
February 22, 2017 at 1:06 pm

#321970

Hello, what is the best method to copy an existing stored procedure and rename so I can make minor modifications to the newly renamed copy? The one I want to reuse is exactly what I need but need to give the copied SPROC a new name and a new internal value, as it's used to help trigger a job once a table is loaded. Thank you, JPQ
March 9, 2024 at 7:07 am

This was removed by the editor as SPAM

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

If all you want is a verbatim copy as a base for your new stored procedure, you can do something like this:

Right-click on the stored procedure you want to use.
Select 'Script Stored Procedure As' > 'Create To' > 'New Query Editor Window'

Then you can modify that one... but be sure to change the name of the stored procedure you're creating.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

The "best method" depends on how you want to do it. Assuming you want to do it via T-SQL, then maybe something like below (I've omitted any error/missing object checking):

DECLARE @current_proc_name nvarchar(100);

DECLARE @new_proc_name nvarchar(100);

DECLARE @proc_source nvarchar(max);

SET @current_proc_name = 'proc1';

SET @new_proc_name = 'proc1_new';

SELECT @proc_source = OBJECT_DEFINITION(OBJECT_ID(@current_proc_name));

SET @proc_source = STUFF(@proc_source, CHARINDEX(@current_proc_name, @proc_source), LEN(@current_proc_name), @new_proc_name);

IF CHARINDEX('ALTER', @proc_source) > 0 AND

CHARINDEX('ALTER', @proc_source) < CHARINDEX('SOURCE', @proc_source)

BEGIN

SET @proc_source = STUFF(@proc_source, CHARINDEX('ALTER', @proc_source), 5, 'CREATE')

END /*IF*/

SELECT @proc_source;

EXEC(@proc_source);

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Amal Aissaoui Newbie Points: 2 More actions · Answer 3

Hello,

Could you please let me know why we are searching for the word "SOURCE" in @proc_source.

Thank you in advance.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Just to bump this question again because it's a great question, I second this question!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 5

This looks like it's supposed to be looking for the @current_proc_name value, not the literal string 'SOURCE', but then WTH do I know?

AND CHARINDEX('ALTER', @proc_source) < CHARINDEX('SOURCE', @proc_source)

seems it should be

AND CHARINDEX('ALTER', @proc_source) < CHARINDEX(@proc_name, @proc_source)

because (I think) he's looking for the pattern "CREATE PROCEDURE [@proc_name]" pattern inside the string, so he can change it to ALTER... and then tweak the body...

but I'll stop conjecturing, because what do I know.

travis48 Grasshopper Points: 14 More actions · Answer 6

Hello,

Follow below My Balance Now steps, it's best method for rename SPROC.

1.In Object Explorer, connect to an instance of Database Engine and then expand that instance.

2.Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

3.Determine the dependencies of the stored procedure.

4.Expand Stored Procedures, right-click the procedure to rename, and then click Rename.

5.Modify the procedure name.

6.Modify the procedure name referenced in any dependent objects or scripts.

This reply was modified 3 years, 2 months ago by travis48.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 7

pietlinden wrote:

This looks like it's supposed to be looking for the @current_proc_name value, not the literal string 'SOURCE', but then WTH do I know?
AND CHARINDEX('ALTER', @proc_source) < CHARINDEX('SOURCE', @proc_source)
seems it should be
AND CHARINDEX('ALTER', @proc_source) < CHARINDEX(@proc_name, @proc_source)
because (I think) he's looking for the pattern "CREATE PROCEDURE [@proc_name]" pattern inside the string, so he can change it to ALTER... and then tweak the body...
but I'll stop conjecturing, because what do I know.

Yeah, I'm not sure how I got the word "Source" in there. Maybe from some template of my own I based the code on.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".