Scripting Anomalies, SSMS 2008, sprocs, and sp_executesql
If you're using SQL Server Management Studio 2008 to manage database objects, you may run into an issue that causes you some grief when you right click on a sproc and either specify the Modify or Script > Create options. Specifically, you may see something that wraps your entire sproc in a call to sp_executesql, like what follows:
Not only does this behavior needlessly complicate things, but it also makes ad-hoc modifications to your sproc a bit more difficult as any ticks that you had in your actual sproc body, now have to be escaped (i.e. ' becomes '') and then you also need to make sure that you keep on top of that with any modifications or edits you may make.
Personally, I'd rather see the scripts generated by SSMS look more like the following – as that's also what they look like under the covers:
As near as I can tell, there are at least one (possibly two) factors involved. First, the exact issue that causes this problem is the scripting option "Include IF NOT EXISTS clause", as shown below.
When that option is set to True is when I've encountered the problem.
Or, more specifically, it appears that when I set that option to True, connect to a SQL Server 2000 server, and generate a script – then I run into the problem with sprocs from SQL Server 2000. More interestingly though, is that this seems to break something internally, because then my scripts for SQL Server 2005 and 2008 servers/objects also get broken.
Seems like a bug to me, but maybe it's just a 'by design side-effect'. Either way, switching the option to False seems to solve the problem for me.