CODEWITHSUNDEEP
sql-server-2008stored-procedurescopy
Yara
Yara

Reputation: 4565

How to copy stored procedure with new name?

For example, I have sp named like 'myStoredProcedure'. And I need to copy this stored procedure with 'myStoredProcedureNew' name in the same server. What is the best practice?

Upvotes: 2

Views: 6919

Answers (1)

Pondlife
Pondlife

Reputation: 16260

One important question is, why are copying the procedure?

If it is because you have changed the logic and want to test it, then you really have a new version of the same procedure. The best approach in that case would be to get the script from source control, change the logic and test it. If it works, you can commit the code to source control and if not you simply revert to the previous version of the script.

If you want to make a second stored procedure using the first one as a starting point or template, then you will indeed have two separate procedures. Here you should get the first procedure from source control, edit it as required to change the procedure name and logic, save it in a separate script file and add the second script to source control. So you would have two scripts in source control, one for each procedure.

If this doesn't answer your question, please provide some more information about why you need to copy the procedure.

EDIT: You explained that you want to do this for 100 procedures and you want to have both old and new procedures available in the database (I assume for backwards compatibility). That sounds like you want to have two different names for the same thing and in that case synonyms could be useful.

You can quickly create synonyms with the new name that reference the old procedures, so you can start using the new names in code. Then when (if?) you're ready to physically drop the old procedures, you can drop the synonyms and rename the procedures. Whether or not this is a good alternative depends on how you plan to manage the transition from old to new procedure names, and how you manage DDL in source control.

If synonyms aren't useful in your case then you can always follow the steps in the second scenario I described. It should be easy to copy and edit the 100 files with a small script and of course as long as you are using source control then it's easy to undo mistakes.

Upvotes: 2

Related Questions