March 16, 2009 at 12:02 pm
Those who answer the question correctly without running the query and he is not a newbie in SQL Server then you really did good. All those who cheated by running the query, you just raised your score but failed to test your real knowledge.
I was wrong in my answer :(.
Amit Lohia
March 16, 2009 at 12:13 pm
Though I was wrong in my answer but now I can understand or I think I understand why it will return NULL.
You can alter the procedure and drop the procedure within the same procedure so this will eliminate two options from the answer
It is not recrusive eliminating the third option also
Now between error message of GO and NULL.
GO will consider as the above T-SQL ready to go as a batch only if it the first word in the line.
For example this will fail,
CREATE PROC
GO AS -- As go it the first word in the line
BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO
EXEC GO
Amit Lohia
March 16, 2009 at 1:09 pm
I was wrong in answering the question. But to me it seems that Procedure name 'GO' is not the cause of concern. If I put something like this
CREATE PROC GOT AS BEGIN
EXEC ('ALTER PROC GOT AS SELECT NULL')
--EXEC GOT
DROP PROC GOT
END
GO
EXEC GOT
As you see, if the second Exec statement is commented out then it doesn't throw the 'NULL', which means that the second execution result is what it shows up when the whole script is run. Also If just the last statment 'EXEC GO' is run twice for the script provided in the question then it throws the right error.
Another point is that if you don't execute the last statement 'EXEC GO' , then you can see that the procedure still exists in the DB. So that exec statement is causing the procedure to live even after the drop statement.
March 16, 2009 at 1:10 pm
I agree with the earlier statement that the choice of a procedure named "GO" was a red herring. That has NOTHING to do with the GO separator. The procedure could have just as easily been named "XYZ" or anything else.
The reason it works is that the procedure is first created then executed. While that procedure is in memory, the stored version is then altered to simply return NULL, then executed, then dropped. All while this is happening, the version in memory is unaffected and when it is completed it of course no longer exists. The version stored in the database was already dropped so there is no trace of it whatsoever.
I agree that it was an interesting bit of a brain teaser made more difficult with using a name of "GO" so as to confuse us with the separator. Good job! :hehe:
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 16, 2009 at 1:23 pm
I got it wrong, but nothing to do with the use of the word 'GO'.
As I calculated in my head what would happen, I had to make a guess as to whether the procedure 'GO' would be cached and so escape being changed by the 'ALTER' when invoked by next line. I opted for the 'nesting level exceeded'. Oh well, never mind; I learnt more by trying to work it out and getting it wrong than blindly running it and getting points.
I too use these QOTDs as a learning exercise (IANAL^HDBA), but simple male (or should that be 'human' in these PC days?) pride means that, ummm, sometimes I *do* run the code to make sure I get the points. Then again, I can just post some comment in the forum!
March 16, 2009 at 2:26 pm
IANAL^HDBA ???
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 16, 2009 at 2:44 pm
IANAL = I Am Not A Lawyer
^H = control-H = backspace 1 char (old Teletype/CPM/MS-DOS control sequence), gives IANA = I Am Not A
DBA = .. this is left as an exercise for the reader !
I considered just using 'IANADBA', but this is not a standard (or maybe it is in this forum!)
Apologies for any confusion ...
March 17, 2009 at 1:08 am
The reason it works is far simpler than it seems.
Essentially 2 calls are made:
1. Create a proc named "GO".
2. Execute that proc.
The code is loaded into memory when EXECuted. The loaded "in-memory" version of the code has no bearing on the PROC being created/modified/run/dropped, and as such remains unchanged.
The result set(s) and messages ultimately belong to the initial in-memory code EXECution, not the modified saved and re-executed instance(s).
Only when execution exits, does the code become vaporware.
The same is true about any PROC mod mid PROC. Changes do not operate until the next time the proc is called. For example, add another "EXEC GO" to the end of the code. You will get the same result, but also an error on the second EXEC, because it no longer exists to be loaded into memory and run again:
"
(1 row(s) affected)
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'GO'.
"
OK, maybe it doesn't sound simple... but the point is the initial code in memory is the control, everything else happens within that instance.
March 17, 2009 at 7:52 am
I strongly object to such obfuscated programming. How many beginners who subscribed to this forum could be misled into thinking "wow ! clever trick I've got to find an occasion to use it".
For this reason, I fully agree with Peter Schott, except that since someone might be forced to deal with lousy code, I think the offender should be lynched and his house burned down to the ground.
OK, if you want to play with brainteasers. However, beginners and even intermediate SQL'ers would benefit a lot by reading topnotch articles such as the Running Count by Jeff Moden. Then you end up with considerably more useful and practical knowledge.
At least, the rewrite by Robert Edgson was useful in demonstrating how to do things properly.
I remember from the early 1980's the great Obfuscated C Code contest. One in particular was formatted so that the listing looked like the pi number, only it was calculating the value of e.
March 17, 2009 at 8:02 am
J (3/17/2009)
I strongly object to such obfuscated programming. How many beginners who subscribed to this forum could be misled into thinking "wow ! clever trick I've got to find an occasion to use it".
The purpose of these brainteasers is not to confuse anyone nor to encourage folks to write code that is not clear, but to get you to fully understand HOW things work. After all, what would be the fun in always working with clear examples that don't encourage you to think?
OK, if you want to play with brainteasers. However, beginners and even intermediate SQL'ers would benefit a lot by reading topnotch articles such as the Running Count by Jeff Moden. Then you end up with considerably more useful and practical knowledge.
As I mentioned, this isn't to confuse newbies, but to give us "old hands" a bit of a noggin nuggin'!
I remember from the early 1980's the great Obfuscated C Code contest. One in particular was formatted so that the listing looked like the pi number, only it was calculating the value of e.
I remember those contests quite well and rather enjoyed them. Again, the purpose was not to encourage obfuscated coding in production use but to provide brain stimulation. Don't get so upset over it!
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 17, 2009 at 8:20 am
I'm just suprised that SQL Server would let a stored procedure modify its own code. That's the real head scratcher here. :ermm:
March 17, 2009 at 8:43 am
I have edited the question to show that this is a bad idea and include a warning to newbies.
I think it's worth pointing this out, not so someone will go do this, but in case they stumble upon it, like mistyping an object identifier, they might better understand what's happening.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 17, 2009 at 3:32 pm
Chris Harshman (3/17/2009)
I'm just suprised that SQL Server would let a stored procedure modify its own code. That's the real head scratcher here. :ermm:
Many code environs can allow this, especially in DBs where the SPs are actually stored within the system, not individual physical disk files. This is not unique only to SQL Server.
The trick is, it isn't modifying its' own code... the in memory version is modifying the saved version... it doesn't need to lock the file for writes, as it has read the whole thing in. Any internal sub-executions are also read into memory and the saved object is still free for ALTER/DROP.
Having a valid reason to do so is another issue entirely, but functionally there is no reason to dis-allow it.
March 17, 2009 at 3:33 pm
Hello,
Basically GO is an OSQL and now an SQLCMD command, and had never been a SQL or T-SQL command.
GO matches the SQL identifiers, so it has to work.
Anyway, as it has been said before, this kind of naming has to be banned 😉
@++ 😉
April 8, 2009 at 6:48 am
You should always know why this is working if you dn't know ask experts
in below examples it creates the procedure with name 'GO' just like 'CREATE PROC uspGO' it takes the character as a procedure name not as a GO keyword.
CREATE PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO
EXEC GO
Abhijit - http://abhijitmore.wordpress.com
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply