• Chris Harshman (3/17/2009)


    I'm just suprised that SQL Server would let a stored procedure modify its own code. That's the real head scratcher here. :ermm:

    Many code environs can allow this, especially in DBs where the SPs are actually stored within the system, not individual physical disk files. This is not unique only to SQL Server.

    The trick is, it isn't modifying its' own code... the in memory version is modifying the saved version... it doesn't need to lock the file for writes, as it has read the whole thing in. Any internal sub-executions are also read into memory and the saved object is still free for ALTER/DROP.

    Having a valid reason to do so is another issue entirely, but functionally there is no reason to dis-allow it.