theboyholty (6/27/2013)
Purely in the interests of throwing the cat amongts the pigeons, you can write a line of code in a stored proc to drop itself you know. I know it doesn't exactly move the discussion along but I found it interesting that a self sacrificial stored procedure such as the one below actually works.create proc usp_suicide_proc
as
begin
print 'goodbye cruel world, its been emotional'
drop proc usp_suicide_proc
end
I'm actually glad you brought this up. I've not done such a thing with stored procedures but have done such a thing with SQL Jobs (self deleting jobs). Such jobs are truly "ad hoc" jobs that need to be executed at a particular time of day and will never be reused as written ever again. It's a bit like usig WAIT FOR TIME except the advantage is that I don't need to keep my machine powered up. It's all in the hands of the server.
Something to be aware of, I've also seen demonstrations by hackers who use the technique to get in and get out without leaving a trace.
--Jeff Moden
Change is inevitable... Change for the better is not.