How to Stop SSMS 2012 from scripting SPs using sp_executesql
sql-server, ssms
Solution
You can't do this without the dynamic SQL because a stored procedure has to be in its own batch. Therefore you can't say:
IF <some condition>
<start a new batch>
The only way to keep that in the same batch is to use `sp_executesql`.
If you're going to script the `DROP` and `CREATE` simultaneously, just do it without the check for object existence. This will give you:
DROP PROCEDURE ...;
GO
CREATE PROCEDURE ...;
GO
Who cares if the `DROP` fails? (It shouldn't, because you just scripted from it!)
If you're scripting this for another system that might have the object, you'll get an error message for the `DROP` when it doesn't, but the `CREATE` will still happen, so you can ignore the `DROP` errors. If you really want you can manually wrap the `DROP` statements in `TRY/CATCH` but I don't think it's necessary.
If you need to do this for a lot of procedures, or if you really need the process to not generate benign errors, I suggest you abandon Management Studio's primitive scripting options and use a 3rd party tool for this. They'll have already dealt with many of the issues you haven't yet come across, but will. I blogged about this:
http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/
Problem
I realise this is a very similar question to Stop SSMS from scripting SPs using sp_executesql? However, they seem to have changed the behaviour with SSMS 2012. If you have the 'Check for existence' option selected, as in: ... it now generates an IF NOT EXISTS for the proc about to be created, as well as an IF EXISTS for the previous drop proc, if, as I usually do, I select the DROP and CREATE option: This forces it to script the CREATE using sp_executesql. It's pointless, since you don't need the IF NOT EXISTS check on the CREATE, if the DROP has just dropped it. It doesn't seem possible to have the one without the other. Any ideas?