Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Stop SSMS 2012 from scripting SPs using sp_executesql

Tags:

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:

enter image description here

... 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:

enter image description here

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?

like image 741
ChrisA Avatar asked May 16 '12 14:05

ChrisA


People also ask

What is Sp_executesql stored procedure?

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.


1 Answers

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/

like image 143
Aaron Bertrand Avatar answered Sep 23 '22 11:09

Aaron Bertrand