Here is the reason why you should NOT name your stored procedures with “sp_” prefix. I was on my way to use this prefix in my last project but one good fellow saved me 🙂 The article contains sample code also (which by unfortunate I have not tried). Through Scooter’s Musings post.
Update: MS SQL 2005 also has this problem.