March 14, 2009 at 2:56 pm
Comments posted to this topic are about the item Polymorphed Proc
March 15, 2009 at 4:26 am
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?
March 15, 2009 at 9:26 am
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”
March 15, 2009 at 11:52 pm
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.
March 16, 2009 at 3:27 am
-> 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
March 16, 2009 at 5:47 am
Seems like a bad idea to name a proc go, even if you can do it. Good to know what happens, but bad practice...
March 16, 2009 at 7:32 am
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."
March 16, 2009 at 7:34 am
we had loads of fun with these kind of queries in the office before I posted this 🙂
March 16, 2009 at 7:35 am
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.
March 16, 2009 at 9:01 am
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."
March 16, 2009 at 9:14 am
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")
March 16, 2009 at 9:41 am
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.
March 16, 2009 at 10:19 am
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.
March 16, 2009 at 11:10 am
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
March 16, 2009 at 11:36 am
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