When you execute sp_detach_db, the procedure cache is flushed for that database. This is probably due to DBCC DETACHDB() [which sp_detach_db wraps], though this command is undocumented.

Edit: You can see this by watching syscacheobjects. I did so with the pubs db on a development sql server. Run a stored procedure, verify it's in the procedure cache, detach, and check the cache. It'll be gone (along with everything else for that dbid).