Nice, if that method can be applied cleanly and consistently across all SPs and all apps.

Unfortunately, many of us work in sloppy production environments with a number of development people with differing abilities, working on different apps, web pages and SPs with different coding standards. In that kind of situation, it's all too easy for folks to forget the O'Neil scenarios and forget to code defensively in both web code and SPs.

On the basis that prevention is better than a painful cure, or just belt-and-braces, I have a background SQL Agent task that runs every night on the "usual suspects" (Surname and address columns) to replace single apostrophes (CHAR(39)) with a Grave accent (CHAR(96))

So, O'Neil becomes O`Neil.

I appreciate this may offend some people, because it should not be necessary, but it does help a little to prevent public-facing apps from failing in an embarrasing way.