Can a SQL Stored Procedure drop itself and continue execution?

Question

Specifically, can I do this?

CREATE PROC AutoDestructiveStoredProcedure
AS
DROP PROC AutoDestructiveStoredProcedure
PRINT 'Still alive.'
GO

Is it a bad practice?

What is the expected behavior? Does it change based on implementation? What would be the difference between executing this in SQL Server, MySQL and Oracle?

Answer 1

Yes, this will work -- at least in SQL Server 2008 R2. It continues executing until the end of the procedure and after that the procedure is gone.

Is it bad practice? I think so. In my mind, the main reason is that it mixes DDL with DML, imposing unexpected side effects on what is normally a well-understood operation (calling a stored procedure).

Unfortunately, I can't answer your question with respect to how it works on MySQL or Oracle.