Renaming a procedure
-
Comments posted to this topic are about the item Renaming a procedure
-
How to modify SP name in Alter Proc script ?
-----------------------------------------------------------------------------
संकेत कोकणे -
sanket kokane (12/11/2012)
How to modify SP name in Alter Proc script ?Good question 🙂
I was also thinking upon this. Instructions in the Question should have stated something like this:
Right-click on the procedure name in the object explorer. Select "script procedure as" and Create to new query window. Change the procedure name to 'rename_by_alter'. Execute the script.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Fortunate enough to get this correct. But the question required lot of reading and thinking!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Instead of saying modify using alter script, Author could have said drop and recreate with new name. Luckily igot it correct.
--
Dineshbabu
Desire to learn new things.. -
dineshbabus (12/11/2012)
Instead of saying modify using alter script, Author could have said drop and recreate with new name. Luckily igot it correct.+1
I thought the same thing ,and was looking for error ;
so I assumed , it's drop and recreate.. that's one way of renaming too ..But, This would require reassigning the permissions on stored procedures afterwards.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Now right click on the procedure and select 'modify', get the alter proc script and change the procedure name to 'rename_by_alter'. Execute the script.
How can we modify the stored procedure name using Alter window...
It will always throw an error stating:
Invalid Object Name 😉
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Similar problem in sp_refreshsqlmodule
-
As happens more often with a Question of the Day: Good idea, but badly executed.
If you exactly follow the instructions, executing the 'alter proc' script after making the change will result in an error (because you are trying to alter a procedure that doesn't exist). And then the statements 3 and 4 will of course also return an error.
Since that option was not available, I had to second-guess what happened when the author submitted the question. The most logical explanation was that he already had a stored procedure 'rename_by_alter' when he started creting this question, so instead of altering the proc he thought he changed, he altered that other already existing proc. I then picked my answers based on that assumption, and I got it right.
Lessons learned:
1. Using sp_rename to rename a programmability item does not change its definition in sys.sql_modules. That's why Books Online recommends to use DROP / CREATE instead.
2. You can't use ALTER PROC to rename a procedure.
3. Submitters of a Question of the Day should always test their questions on an empty database before submitting their question.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice question, but could have been worked out a little better.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (12/12/2012)
Nice question, but could have been worked out a little better.+1
Thanks for the quesion
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Thanks Hugo for your insights.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
good explanation Hugo...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
I didn't even notice the conusion about the 'rename_by_alter' instructions. I was already aware of the behaviour of sp_rename/sp_helptext, so0 that ruled out the 1st option. 2 and 3 were ruled out because the third answer related to the first procedure, so couldn't possibly be correct for the completely unrelated second procedure. Which only left one answer 🙂
-
Knowing already that using sp_rename does not change its definition, and with there being no error scenario in the answers, I had to take the only possibly logical answer available. Thereby presuming a DROP/CREATE scenario, rather than ALTERing a "procedure that does not exist", was the intention of the author.
Good question, just badly implemented.
Thanks.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply