SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Polymorphed Proc


Polymorphed Proc

Author
Message
emanuel.unge
emanuel.unge
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 20
Comments posted to this topic are about the item Polymorphed Proc
DannyY
DannyY
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 167
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?
Robert Edgson
Robert Edgson
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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”
mverma4you
mverma4you
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 226
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.
PRABIR GHOSH
PRABIR GHOSH
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 122
-> 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
SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3981 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...
Rudyx - the Doctor
Rudyx - the Doctor
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18445 Visits: 2506
It's a cute brain twister that I enjoyed with morning coffee at home before going into the office. Smile

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
emanuel.unge
emanuel.unge
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 20
we had loads of fun with these kind of queries in the office before I posted this Smile
John Laskey
John Laskey
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 238
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.
Rudyx - the Doctor
Rudyx - the Doctor
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18445 Visits: 2506
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search