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 Saturday, March 14, 2009 2:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 1:54 AM
Points: 26, Visits: 20
Comments posted to this topic are about the item Polymorphed Proc
Post #675984
Posted Sunday, March 15, 2009 4:26 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 7:00 AM
Points: 438, Visits: 136
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?
Post #676073
Posted Sunday, March 15, 2009 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 1:16 PM
Points: 38, Visits: 26
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”






Post #676104
Posted Sunday, March 15, 2009 11:52 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, October 10, 2013 10:15 AM
Points: 459, Visits: 182
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.
Post #676267
Posted Monday, March 16, 2009 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:57 PM
Points: 7, Visits: 102
-> 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
Post #676325
Posted Monday, March 16, 2009 5:47 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Seems like a bad idea to name a proc go, even if you can do it. Good to know what happens, but bad practice...
Post #676385
Posted Monday, March 16, 2009 7:32 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:18 AM
Points: 3,193, Visits: 2,289
It's a cute brain twister that I enjoyed with morning coffee at home before going into the office. :)



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #676481
Posted Monday, March 16, 2009 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 1:54 AM
Points: 26, Visits: 20
we had loads of fun with these kind of queries in the office before I posted this :)
Post #676485
Posted Monday, March 16, 2009 7:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, June 7, 2013 6:57 AM
Points: 642, Visits: 200
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.
Post #676489
Posted Monday, March 16, 2009 9:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:18 AM
Points: 3,193, Visits: 2,289
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 [;]




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #676587
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse