Polymorphed Proc

  • Comments posted to this topic are about the item Polymorphed Proc

  • The only odd thing is the use of the word GO to name the procedure...

    However, GO is not listed as a reserved word in SQL, so why not?

    The first batch creates the procedure

    The second runs the procedure that was created in the first batch

    The procedure deletes itself at run-time.

    Any good reasons why it shouldn't work?

  • A classic case of poor naming convention

    We have used the name “GO” twice

    1st “we create a stored procedure “GO”

    lets change its name to “My_Proc_GO”.

    2nd we use the batch termination command “GO”.

    The command “GO” is not a T-SQL command but a command recognised by SSMS query , SQLCMD to bundle up several T-SQL commands together in a batch. it is not a reserved word because it is not a t-SQL command

    Let us re-write the Question with a better naming convention with some comments added .

    CREATE PROC My_Proc_GO

    --creates procedure “My_Proc_GO”

    AS

    BEGIN

    EXEC ('ALTER PROC My_Proc_GO AS SELECT NULL')

    --alter procedure “My_Proc_GO” to run select statement

    EXEC My_Proc_GO

    -- run the procedure “My_Proc_GO “ this displayes results to screen after it has been modified

    DROP PROC My_Proc_GO

    -- Drop the procedure

    END

    GO

    --end of batch

    EXEC My_Proc_GO

    --Does what is says on the tin executes the stored proc

    Next let us look at stored procedure if we were to change the last line to

    --EXEC My_Proc_GO

    Then script the creation of stored proc from SSMS

    CREATE PROC [dbo].[My_Proc_GO]

    --creates procedure “My_Proc_GO”

    AS

    BEGIN

    EXEC ('ALTER PROC My_Proc_GO AS SELECT NULL')

    --alter procedure “My_Proc_GO” to run select statement

    EXEC My_Proc_GO

    -- run the procedure “My_Proc_GO “ this displays results to screen

    DROP PROC My_Proc_GO

    -- Drop the procedure

    END

    GO

    If we were to again modify this stored proc and comment out the Drop stament

    --DROP PROC My_Proc_GO

    The run the stored proc we get

    ALTER PROC [dbo].[My_Proc_GO] AS SELECT NULL

    So the command that finally runs is a simple

    select null

    put back the

    DROP PROC My_Proc_GO

    A a better name would be “My_proc_DeleteSelfWhenRun”

  • There is no strange in this procedure. First you creating the proc and altering the logic using Execute SQL statement. then you are calling the procedure and then droping it.

    as per the instruction given in the procedure first it's altering the procedure logic and update the information which is stored in the sytem table about the procedure. And then you are executing the procedure which will pull the instruction available in the system table and then returing the value.

  • -> Procedure 'Go' will create with alter and and drop statement.

    -> In the last statement procedure will execute.

    -> Execution step :

    i) procedure is altered in order to return NULL value

    ii) then execute the procedure again

    iii) drop the procedure by it self.

    So when procedure executing within it self it will return new alter procedure body which is returning NULL

  • Seems like a bad idea to name a proc go, even if you can do it. Good to know what happens, but bad practice...

  • It's a cute brain twister that I enjoyed with morning coffee at home before going into the office. 🙂

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • we had loads of fun with these kind of queries in the office before I posted this 🙂

  • EXEC GO should work exactly as it does.

    Let me try to explain from my point a view. Lets break down what we are asking SQL to do:

    First we create a procedure called "Go". (Not good naming but perfectly ok in the eyes of SQL). The procedure "GO" contains the following:

    CREATE PROC GO AS

    BEGIN

    EXEC ('ALTER PROC GO AS SELECT NULL')

    EXEC GO

    DROP PROC GO

    END

    GO

    Now the statement GO after the "END" statement. What does GO do here? Go is not a T-SQL statement. It is interpreted as a signal that they code above it should be sent as a batch of Transact-SQL statements.

    Now SQL has been told to execute what we just sent with the statement

    EXEC GO and it does...

    The SQL engine executes the procedure:

    ALTER the procedure and in this case executes the select statement "SELECT NULL" (you could have but any select statement here). This in turn puts a NULL in the return buffer so to speak. The next statement does exactly what it was told to do. The last step in the SP is to delete itself and it does. A NULL is still in the buffer and is returned.

    Just my 2 cent worth.

  • 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 [;]

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • 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")

  • 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.

  • 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.

  • 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

  • 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... 🙁

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply