Renaming a procedure
-
Hugo Kornelis (12/12/2012)
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.
+1
+1
+1
+1
-
Hugo Kornelis (12/12/2012)
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.
+1
As often happens with a QotD, the real lesson is learned by reading Hugo's contribution.
Thank you.
-
sipas (12/12/2012)
Hugo Kornelis (12/12/2012)
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.
+1
As often happens with a QotD, the real lesson is learned by reading Hugo's contribution.
Thank you.
+1
Strongly agree!
~ 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
-
right...
I learned a lot from Hugo's explanation in discussion 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
sipas (12/12/2012)
As often happens with a QotD, the real lesson is learned by reading Hugo's contribution.
Thank you.
Amen to that!
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font] -
Hugo Kornelis (12/12/2012)
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.I've never quite understood that. To my mind it's a bug. Presumably MS don't think that it is, as they've documented the behaviour, but what justification is there for it?
-
Toreador (12/12/2012)
Hugo Kornelis (12/12/2012)
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.I've never quite understood that. To my mind it's a bug. Presumably MS don't think that it is, as they've documented the behaviour, but what justification is there for it?
In my opinion, it would have been a bug if it had not been mentioned in the documentation. I better think it as limitation of sp_rename for procedure.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Toreador (12/12/2012)
Hugo Kornelis (12/12/2012)
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.I've never quite understood that. To my mind it's a bug. Presumably MS don't think that it is, as they've documented the behaviour, but what justification is there for it?
The factual answer: No idea.
The speculative answer: Documenting behaviour is one way to "close" a bug deemed not important enough to warrant fixing.
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/ -
Totally bogus QotD!! If you follow the directions you cannot get the results. If you stray from the directions then you are making up your own QotD. This is a real turn off to me who likes the directions and questions a bit more accurate than I have seen lately in the QotD.
-
I agree with demanfox if it is mentioned in the doc then its the limitation of sp_rename not a bug.
🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Hugo Kornelis (12/12/2012)
The speculative answer: Documenting behaviour is one way to "close" a bug deemed not important enough to warrant fixing.Makes sense. I wish I could get away with that approach!
-
Sorry but this is poorly worded and poorly thought out question. How do you alter a procedure that does not exist?
-
its already discussed 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
IMO - Its a bug!
I complained to MS about this a couple of years ago and loads of other people have too (see MS Connect site) hence the document update.
I think a documentation update is a bit of a cop out but other than as a quick fix with a proper fix in the next release but this has been around since SQL 2005 at least (probably longer - I don't know)
A system table refresh following a call to the rename function shouldn't be too hard - should it?
Dave
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com -
As most have already said, I had to guess since the altering of a non-existent procedure results in an error. luckily I guess correctly. 🙂
Thanks,ToddR
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply