THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Should SQL Server Have The CREATE [OR REPLACE] PROCEDURE Syntax?

I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage
First the advantages
 
Advantage
When scripting out a database you don’t have to generate if exists.....drop statements

When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?
Published Wednesday, May 07, 2008 12:53 PM by Denis Gobo
Filed under:

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

 

andyleonard said:

Hi Denis,

  I'm used to the If Exists syntax, so that drives my coding style and determines the things I worry about. One advantage of dropping and recreating stored procedures in SQL Server is this allows me to see the last datetime the procedure was modified at a glance - using the CreateDate.

:{> Andy

May 7, 2008 12:04 PM
 

Alexander Kuznetsov said:

Hi Denis,

First of all, I think that all stored procedures must be in source control, which takes care of the worry about overwriting each other's code. Also I think there are very good tools for deployment, so I don't have to create deploy/rollback scripts manually.

Also let me share a small trick in pseudocode:

IF procedure does not exist BEGIN

 Issue dynamic SQL command to create a stub procedure with this name

END

ALTER PROCEDURE

<all your code goes here>

May 7, 2008 12:14 PM
 

Denis Gobo said:

Andy,

In SQl server 2005 you have both the create and modification date available. run this

create proc Testcreate

as

select getdate()

go

waitfor delay '00:00:10'

go

alter proc Testcreate

as

select getutcdate()

go

select create_date, modify_date,* from sys.objects where name = 'Testcreate'

May 7, 2008 12:15 PM
 

Denis Gobo said:

Alexander,

>>Also I think there are very good tools for deployment, so I don't have to create deploy/rollback scripts manually.

FYI

At some companies, you have to supply scripts and someone else will hit the magic F5 key. If you messed up, they will still come back to haunt you  :-)

May 7, 2008 12:17 PM
 

Alexander Kuznetsov said:

Yep - that's how it works in our company. I use a tool to create deploy/rollback scripts, it compares a copy of production against a database created from scripts checked in source control. I submit these scripts, and the DBAs use tools to test my create and rollback.

May 7, 2008 2:03 PM
 

Vern Rabe said:

I would like to see it. Our current standard is to (conditionally) drop and create, but that standard was decided upon pre SQL Server 2005 so that we could determine when a proc was last modified (via sysobjects.crdate). We also use schemas for execute permissions instead of explicit procedure permissions, so dropping and recreating doesn't cause any permission issues. That being said, the option of using the CREATE [OR REPLACE] would be handy when schema level permissions aren't approprate, plus it allows having meaningful create_date AND modify_date.

May 7, 2008 2:11 PM
 

Hugo Kornelis said:

Hi Denis,

Regarding the disadvantage of accidentally overwriting each others code - if you habitually put an IF EXISTS ... DROP at the start of each script, the exact same happens...

May 7, 2008 2:41 PM
 

David L. Penton said:

I agree with Hugo and Vern.  The issue of two developers doing this is a non-issue as this can happen today.  And, I like the idea of the metadata being meaningful.  Of course, as soon as someone DROPs/CREATEs an object, you lose that information.

May 7, 2008 3:45 PM
 

Chris J. Breisch said:

Your "disadvantage" point is disingenuous.  I run into the exact same problem if I use IF EXISTS ... DROP.

Oh, Hugo beat me to it.  He's correct.

So, you've pointed out an advantage and a disadvantage that also applies to the current scenario, so isn't really a disadvantage.

Sounds like to me that you've answered your own question.  There are only advantages to having it.

May 8, 2008 8:19 AM
 

Denis Gobo said:

>>Sounds like to me that you've answered your own question.  There are only advantages to having it.

I just love it when I answer my own question.

So, only advantages? Why is it not in SQL server yet, Is there a connect link requesting this? It will get my vote

May 8, 2008 11:02 AM
 

Saggi Neumann said:

Hey Denis,

A better post on connect would an option be to enable versioning of SQL code (as an option) like we had on DTS packages in 2000.

What do you think?

May 8, 2008 1:20 PM
 

AaronBertrand said:

Denis: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127219

Saggi:

You can kind of do this with numbered procedures, but this feature is going away, rather than being extended to other objects.

May 8, 2008 2:34 PM
 

Denis Gobo said:

Aaron,

Thanks, I casted my vote (FWIW)

May 8, 2008 2:55 PM
 

tduffy77 said:

It can only help. In our change control system, we don't allow drops in production. The developers want to reuse scripts across all environments (as they should), and alters won't work for new objects on databases freshly restored from production. Create replace (alter) would fit the bill nicely.

May 8, 2008 4:42 PM
 

Saggi Neumann said:

Aaron: I actually thought about it a little after posting the comment, and it can probably be achieved quite gracefully with DDL triggers, saving versions of stored procs, functions, views and table definitions...

May 9, 2008 11:40 AM
 

Alexander Kuznetsov said:

Saggi,

Why would you want to do that? Any source control system (my favorite one is Subversion) will do it for you, and much much more, and all your source code should be checked into a source control system anyway.

May 12, 2008 9:07 PM
 

Saggi Neumann said:

Alexander,

As a SQL Server consultant, I can tell you that many of my customers don't have their SQL code in source control. Some of them don't even have their application code in source control (yeah... i know...).

So - you're right - all source code should be managed in a source control environment. But since source control unfortunatly isn't used everywhere, deploying DDL triggers to at least keep things under control in the SQL domain is a pretty straightforward and fast solution.

May 13, 2008 4:01 PM
 

Firefight said:

In Oracle you don't want to go around dropping and recreating objects because you lose all of the permission settings that you gave users for those objects. SQL Server doesn't have that sophisticated of a security model so it's not so much of an issue.

December 22, 2008 3:01 PM
 

Almander said:

Firefight, please crawl back under your rock.

We all face the same types of problems dropping a procedure in Oracle as we do in Sql Server. When we drop the object, we also drop the permissions. This causes problems (unless you are dbo -grin).

This is why I vote 100% that Sql Server should have (and should have had) a CREATE OR REPLACE functionality (yes, like what Oracle has).

January 28, 2009 4:41 PM
 

database_programmer said:

Oh yes. I've just started work on an SQL Server project after years with Oracle and cannot believe how 'toy' it is in comparison.

CREATE OR REPLACE is high up on my peeve list. The IF EXISTS syntax is just wrong. Unnecessarily complicated scripts, possibilities for typos between the DROP and the CREATE. Horrid.

Oh, and T-SQL SUCKS in comparison to PL/SQL. Bigtime.

February 13, 2009 7:37 AM
 

Tom said:

I, too, would like to see Create or Replace.

In the mean time you can do something like the following, which would dynamically create the appropriate create or alter statement.

Only caveat...you need to replace qoute(') with two qoutes('') since we are setting the text into a variable.

DECLARE @CreateOrAlter NCHAR(17)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))

SET @CreateOrAlter = 'ALTER PROCEDURE'

ELSE

SET @CreateOrAlter = 'CREATE PROCEDURE'

DECLARE @SPCode NVARCHAR(MAX)

SET @SPCode = @CreateOrAlter+'

[dbo].[mySP]

(

@Argument1 NCHAR(64)

)

AS

BEGIN

SELECT ''Hello, World!''+@Argument1

END

'

EXECUTE sp_executesql @SPcode

March 7, 2009 5:14 AM
 

Swetha said:

Thank you for the Idea, however i would like to dynamically create the appropriate Create or Alter the below function. Please help me

CREATE FUNCTION [dbo].[ConcatDocumentProperties]

(

     @DocumentID int

)

RETURNS nvarchar(4000)

AS

BEGIN

-- return variable

declare @propertyItems nvarchar(4000)

--Build xml string of property items

Select @propertyItems = null

SELECT @propertyItems = Coalesce(@propertyItems + '', '') +

                                   '<PropertyID><![CDATA[' + convert(varchar,CustomDocumentProperty.ID) + ']]></PropertyID>' +

                                   '<PropertyTitle><![CDATA[' + CustomDocumentProperty.Title + ']]></PropertyTitle>'+

                                   '<PropertyItemID><![CDATA[' + convert(varchar,CustomDocumentPropertyItem.PropertyItemID) + ']]></PropertyItemID>'+

                                   '<PropertyItemTitle><![CDATA[' + CustomDocumentPropertyItem.PropertyItemTitle + ']]></PropertyItemTitle>'

FROM   CustomDocumentProperty,

      CustomDocumentPropertyItem,

      DocumentCollectionView,

      CustomDocumentPropertyReferences

WHERE  CustomDocumentPropertyItem.PropertyID       = CustomDocumentProperty.ID

AND    CustomDocumentPropertyItem.PropertyItemID   = CustomDocumentPropertyReferences.PropertyItemID

AND    DocumentCollectionView.DocumentID           = CustomDocumentPropertyReferences.DocumentID

AND    DocumentCollectionView.DocumentID           = @DocumentID

ORDER BY CustomDocumentProperty.ID,CustomDocumentPropertyItem.PropertyItemID

     RETURN @propertyItems

END

GO

April 10, 2009 10:22 AM
 

Ron said:

I'm in favor of the concept, but wonder about the syntax.  Shouldn't there be an ANSI standard adopted first?  If this is such a valuable addition, why not add it to the standard?  On the other hand, maybe it is best just to use an existing commercial implementation of another vendor and then push to make that the standard.

July 27, 2009 12:30 PM
 

Scott said:

Another disadvantage to dropping and re-creating a stored procedure is the dependencies.  If you drop a stored procedure it could wipe out a dependency that won't get restored when you re-create the stored procedure.  I often use the dependencies to determine the impace of changing a table.

Does anyone know if this CREATE OR REPLACE feature made it into Sql Server 2008?  I haven't found anything stating one way or the other.

August 19, 2009 10:40 AM
 

DenisP said:

hey gurus, might come late for the topic - how do you go about creating with replace option functions used in column default constraints? Don't even think about dropping them with IF EXSITS check - SQL server simply won't let you do that. IMHO, the Oracle's "CREATE [OR REPLACE]" - is the only way to go!

July 20, 2010 11:53 AM
 

Roman said:

It didn't make to 2008 nor 2008 R2.

Hopefully Denali? Unfortunately it is not in the list of features. :(

I wish Microsoft would plesantly surprise us with this long-needed feature!

Besides I am sure it is much easier for them to implement than some worthless features like increasing number of indexes you can create on the table!

October 25, 2011 10:44 AM
 

Bernd Kriszio said:

More important would be to allow create/alter procedure inside if statements without the need to use EXEC.

October 30, 2011 11:35 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement