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?
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
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/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With