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

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

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>

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.

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.

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

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.

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.

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?

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.

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.

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

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.

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.

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.

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

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.

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

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

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.

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.

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!

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!

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