December 12, 2012 at 7:31 am
Hugo Kornelis (12/12/2012)
As happens more often with a Question of the Day: Good idea, but badly executed.3. Submitters of a Question of the Day should always test their questions on an empty database before submitting their question.
I'd like to add:
4. Have someone proofread who doesn't already know the answer you're hinting at.
I didn't understand the question at all. I guessed (and was wrong) just so I could see this discussion.
December 12, 2012 at 9:17 am
So, as a follow-up question to this somewhat unfortunately worded but very useful QotD: is there a way to query system views to find SPs, renamed with sp_rename, whose sp_helptext output no longer matches the current SP name?
This is a bug: the posted recommendation to DROP/CREATE the SP breaks permissions following a rename, which is why I have preferred to use sp_rename.
Rich
December 12, 2012 at 9:18 am
Confusion led to testing, which in turn led to guessing.
The wording of today's QotD is very confusing, and trying to RENAME a PROC using ALTER led to error, which left guessing as the only option for answering. Fortunately, I got it right.
I agree with all others, we should not be learning from other user's explanations, but from the questions themselves.
Saludos,
December 12, 2012 at 10:05 am
Mike Dougherty-384281 (12/12/2012)
. . . .
I didn't understand the question at all. I guessed (and was wrong) just so I could see this discussion.
+1
December 12, 2012 at 10:28 am
DugyC (12/12/2012)
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.
+1.
Agreed.
Rob Schripsema
Propack, Inc.
December 12, 2012 at 10:30 am
Rob Schripsema (12/12/2012)
DugyC (12/12/2012)
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.
+1.
Agreed.
+1
December 12, 2012 at 10:32 am
Hugo Kornelis (12/12/2012)
As happens more often with a Question of the Day: Good idea, but badly executed.
A good idea badly executed is a good description of this question.
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.
Actually no, statement 3 will return an error but statement 4 will execute without error and return NULL.
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.
I really wanted a "none of the above" answer to choose! And like you, i noticed that option wasn't available, and looked for the least complicated mistake for the author to have made; so chose the fourth option (as the third required a more complicated mistake, and I couldn't imagine any reasonable way for getting either of the other two options) and got it "right". But I was sorely tempted to just pick an answer at random to see what on earth the author was doing instead of thinking about it.
Tom
December 12, 2012 at 12:18 pm
Lokesh Vij (12/11/2012)
Fortunate enough to get this correct. But the question required lot of reading and thinking!
I'll second that one... and I've run into this "funness" before too. Still good question... now I need some advil.
December 14, 2012 at 7:29 am
sanket kokane (12/11/2012)
How to modify SP name in Alter Proc script ?
Good Point... 🙂 However since the four options provided did not had any "Error" option, the obvious thought came to my mind that it should be CREATE and not ALTER. Somehow was able to get point.
In between, good QOTD.
December 19, 2012 at 6:51 am
I got this right although, as mentioned by others, none of the answers is correct. The first 2 are OK and both return 'rename_test', but using the ALTER script to rename a procedure doesn't work, and the only referenced procedure name from sp_helptext is 'rename_by_alter'. Since this fails, the final step returns NULL and doesn't name any procedure.
So the correct answer should have been....
rename_test, rename_test, reanme_by_alter, NULL!
Derek
December 19, 2012 at 7:01 am
Also, there's a small typo. I just found in the provided answer:
the catalog view has an underscore: sys.sql_modules, missing in the answer.
Rich
April 5, 2013 at 8:23 am
Lokesh Vij (12/11/2012)
Fortunate enough to get this correct. But the question required lot of reading and thinking!
+1
Got it right but boy did it take a while!! 🙂
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply