Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!

T-SQL Snack: Why Does SSMS Generate Create Scripts With EXEC dbo.sp_executesql?

Introduction

I've been meaning to blog about this for a while and a question from a team member prompted this post (thanks Allan!)

SSMS Scripting Options

In SSMS you can script a stored procedure - right-click a stored procedure, hover over Script Stored Procedure As, hover over Create To, and select New Query Editor Window:

Sometimes the script is generated as shown:

The reason? Your scripting options (Tools-->Options-->Scripting in SSMS 2005 [shown below], Tools-->Options-->Sql Server Object Explorer-->Scripting in SSMS 2008 R2) are set to generate IF NOT EXISTS clauses:

Set this to False to generate stored procedure T-SQL that appears as shown here:

What I'd Like

I would like the If Not Exists statement and the T-SQL without the call to sp_execute. Something like:

IF NOT EXISTS (SELECT s.name + '.' + p.name
                        FROM sys.procedures p
                        INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
                        WHERE s.name = 'dbo'
                          AND p.name = 'uspGetBillOfMaterials')
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN...

In a recent email exchange with the SSIS Developer Team at Microsoft, I shared a bunch of things I would like in SSIS. Since I was asking for so much I included a request for a pony. Matt Masson (Blog - Twitter) sent me this image in reply:

The clincher in this image is the inset of a horse with the caption "NOT YOURS". I still laugh out loud whenever I look at this!

:{> Andy

Published Monday, July 19, 2010 8:00 AM by andyleonard

Filed under: SQL Server, SSMS, T-SQL Snack, No Pony

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

	Ben Thul said: No that there's anything wrong with your 'if not exists' block, but you're working too hard. "if (object_id('your_schema.your_proc'), 'P') is not null)" should do the trick July 19, 2010 7:24 AM
	Aaron Bertrand said: The reason you can't is because, at least today, the CREATE PROCEDURE must be in its own batch, and this would kill any surrounding IF blocks. What would be nice is if you could point SSMS at the target database so that, like data dude / SQL Compare etc. it would know whether it needed to be a CREATE or ALTER. Or if they would actually follow through with CREATE OR REPLACE syntax (without disrupting permissions and dependencies). July 19, 2010 7:54 AM
	Ben Thul said: You're absolutely right; that's why you put a batch separator between the drop and the create. July 19, 2010 9:48 AM
	Eric Wisdahl said: Man, that Matt Masson guy is MEAN! :-P Any hints as to what else you requested from them? July 19, 2010 11:53 AM
	andyleonard said: Hi Eric, I need to blog about this: I told them they broke the Package Configurations / Command Line order of operations and need to fix them. The command line should always win over an external configuration. :{> July 19, 2010 12:02 PM
	AaronBertrand said: Ben, the point is that the tool won't do this for you automatically. Those of us who can write and modify our own scripts are not tripped up by the crap that SSMS can produce in some cases; the ones we're worried about are those who can't. July 19, 2010 1:38 PM
	Ben Thul said: My comment is related only to the manually generated existence check that was placed at the end of the post. I'm sorry if it was taken as commentary on the whole post (which, in retrospect, I can see how it might be interpreted that way). July 19, 2010 5:43 PM
	Ben Thul said: I'd been meaning to put this up for a while and now is as good a time as any: http://blog.semperoccult.us/2010/07/scripting-your-database-for-fun-and.html The formatting leaves much to be desired (I blame blogger and will try to figure it out), but the code shows that the so-called Shangri-la can be obtained programmatically. Comments encouraged. July 19, 2010 9:51 PM
	LadyRuna said: I wish they hadn't changed the scripting from how it worked back in SQL 2000. I preferred to have the "If exists... then drop <item you're going to create>" at the beginning of the script so that we would be able to save one copy of each script rather than an ALTER and CREATE version to manage updates / changes to the scripts over time. I think Microsoft's reason for this scripting change was because it's trying to encourage you to choose ALTER instead of CREATE if the item already exists because whenever you recreate an object it obtains a new ID and could cause issues in some cases. (although I've never seen issues with recreating an object, I once read that supposedly it does and that you're supposed to ALTER the item after it's been created rather than drop and recreate.) July 22, 2010 11:53 AM