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”