Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Polymorphed Proc Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 9:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 5:52 AM
Points: 453, Visits: 139
rudy komacsar (3/16/2009)
and yet again to prove why it does work:

CREATE PROC [;] AS BEGIN
EXEC ('ALTER PROC [;] AS SELECT NULL')
EXEC [;]
DROP PROC [;]
END
GO
EXEC [;]


Yes, but you had to use square brackets - making it obvious-, whereas GO worked like a "charm" (please read "later nightmare")

Post #676608
Posted Monday, March 16, 2009 9:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Using the name "GO" is a red herring here and is not so strange as the fact that this is both recursive and self-modifying code. Generally, self-modifying code is not good to use because of the added complexity in maintenance.

Sure it works:
The script creates the proc with three steps inside.
The script runs the proc.
While running,
The proc alters itself to select NULL
The proc runs itself, returning the NULL
The proc drops itself.
Post #676658
Posted Monday, March 16, 2009 10:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:02 PM
Points: 841, Visits: 1,285
Strangely enough, my first choice for an answer - "The DBA who wrote this is locked out of all accounts and escorted out of the department" - was not an option. I agree that it's an interesting exercise, but in general someone who writes something like this for production use should not be allowed to code SQL for a long time. :)

Of course, that being said, I think that Red Gate had a blog posting recently that read very much like an obfuscated SQL contest entry. The article tried to use just about every reserved word possible to build tables, name columns, populate the tables, create the procs, etc. It was amusing, but I'd sure hate to have to maintain anything really written like that.



Post #676690
Posted Monday, March 16, 2009 11:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
If you look in SSMS Options under Query Execution, you'll find that the GO batch separator can be changed.

I wouldn't recommend it though.:D


Derek
Post #676740
Posted Monday, March 16, 2009 11:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:37 PM
Points: 2,392, Visits: 17,941
Derek Dongray (3/16/2009)
If you look in SSMS Options under Query Execution, you'll find that the GO batch separator can be changed.

I wouldn't recommend it though.:D


Yeah - I can see someone changing it to SELECT...
Post #676779
Posted Monday, March 16, 2009 12:02 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174
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 .



Kindest Regards,

Amit Lohia
Post #676809
Posted Monday, March 16, 2009 12:13 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174
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



Kindest Regards,

Amit Lohia
Post #676823
Posted Monday, March 16, 2009 1:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 8:09 AM
Points: 51, Visits: 118
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.
Post #676856
Posted Monday, March 16, 2009 1:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:25 AM
Points: 1,127, Visits: 546
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!



"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Post #676858
Posted Monday, March 16, 2009 1:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:37 AM
Points: 488, Visits: 405
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!
Post #676866
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse