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
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
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).
You’re absolutely right; that’s why you put a batch separator between the drop and the create.
Man, that Matt Masson guy is MEAN! 😛
Any hints as to what else you requested from them?
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.
:{>
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.
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).
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.
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.)
Apparently this has changed again recently in SSMS 2014.
Now, if you script a drop and create with ‘Check For Object Existence’, it:
Creates the "IF EXISTS (…) DROP PROCEDURE" block
Creates an IF NOT EXISTS (…) block that uses dbo.sp_executeSQL to create a BLANK stored procedure with the specified name,
And finally uses ALTER PROCEDURE to alter the blank stub to contain the final stored procedure script.
Apparently Microsoft is trying REALLY hard to avoid any GO statements in their script.
Unfortunately, a GO is still required before the ALTER PROCEDURE and the script, as generated by SSMS2014 FAILS with the message
‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.
Really great help.. i thought re-installing the sql server will help..but the problem remained as it was. but this artcile solved it simply thanks
Yeah, I would tell Microsoft, no the pony is not mine, but that does not give you the right to continually abuse it!
The "IF EXISTS" is my "go to" for keeping a full, cumulative change log of scripted objects for our deployments. Now I either need to manually add them, or monkey with the sp_executesql to get it back. The CREATE/ALTER is nice to finally arrive, as Oracle has had this since the beginning, but I cannot believe they would expect this as the first statement in the batch. I could understand the concern for reusing OBJECT_IDs, but we do not tie anything in our database to the OBJECT_ID. Of course, this may be used extensively for some of the DMVs and might factor in for your db security.
Thanks Microsoft. Argghhhh!!!!!!
Ok. Maybe I’m the only person in the whole world that has to modify existing SQL Stored Procedures, but I do.
In 2000/2008 the scripts work perfectly – at least the way I think they should work.
In 2012, the issues is that with ALTER PROCEDURE or sp_executesql, the SQL is a string variable and INTELLISENSE DOES NOT WORK. Plus, it looks like S***!
Why, oh why, does Microsoft think they need to change everything every time they release a new version of a product! Forces us to re-learn stuff we already know.
It sounds like MS is deliberately making things difficult to support some unannounced agenda. The need to create scripts that don’t contain GO statements has (to my knowledge) never been justified. It has certainly not been heavily pressed for by the user community. The current implementation makes typical maintenance much more complicated with no tangible benefits.
As for drop and create ‘always working’, so does this (without the object_id changing or needing to re-attach things like extended properties…):
if (object_id(‘your_schema.your_proc’), ‘P’) is null) exec(‘create your_schema.your_proc THROW 50001, ”Not implemented”,1;’);
Alter procedure your_schema.your_proc….