Hello,

The "CREATE or REPLACE" like syntax is not part of SQL2005. We are considering it for a future release.

Thanks for your feedback.

Sameer Verkhedkar
sameerv@microsoft.com
[MSFT]

The lack of this feature shouldn't just be "considered". No wonder folks talk about SQL Server as if it were a joke.

Seriously guys? Just put it in there. Don't make us wait for Katmai, only to find out it's not in there either, add it to a Service Pack for crying out loud. It's almost embarrasing that we don't have that one small feature.

Please add this feature as a priority

Perhaps a "create procedure [...] with drop_existing" syntax? This would be consistent with "create index." In addition to simply making scripting more convenient, this option would allow a procedure to be replaced without the need to replace all permissions, as happens with a drop/create.

Hello

Thank you for your continued feedback on this feature. We will not be able to support CREATE OR ALTER functionality in the upcoming SQL Server 2008 release. We are actively tracking this feature request and will consider it for a future release of SQL Server.

Thanks

-- SQL Server Engine Team

While your response doesn't surprise me, it is a complete joke. I requested this feature in November of 2005 so you've had ample time to work this into the SQL 2008 release.

The good news is that this issue has become irrelevant since I switched to Ruby on Rails and MySQL in January of 2006. Why? Because of Microsoft's lack of direction and complete lack of concern for critical developer requests - like implementing a REAL O/R mapping solution just to name one.

Sadly, I would agree with the statement that "While your response doesn't surprise me, it is a complete joke."

The SQL Development team had about 26 months to respond to the initial comment/request ... There would seem to be two possibilities.
1 - They didn't KNOW about the request (if not, then WHY NOT ? What's the point of us developers providing feedback requests ?)
2 - They didn't CARE about the request (if so, then why was there no further feedback on the connect-site?)

Unlike the "major" new features like "SQLCLR , FILESTREAM or Spatial Types, surely this is a relatively-small feature to add.
This isn't so much "new functionality", but is more "combining two features into one simpler syntax"

I would agree that some sortof "WITH DROP_EXISTING" would be a nice approach

Hi,
We understand your concern regarding the CREATE OR ALTER request. We value all customer feedback and sometimes a feature doesn't get into the product due to scoping/resources/priority. Just to give you an idea, we have over 2000 programmability requests to consider for the next version of SQL Server. For every reelease, we go through various scenarios, evaluate customer requests and come up with the features that can fit the schedule given the resources. Sometimes we make painful cuts and end up deferring a request to future.
Anyway, we are definitely evaluating adding the syntax for the next major version of SQL Server. One of the challenges is to decide which DDLs to support first. We cannot add CREATE OR ALTER for every single DDL since that will require lot of work and testing. We will look at scoping the syntax support and provide value to developers for the common scenarios. Hope this helps.

--
Umachandar, SQL Programmability Team

Hello, Guys
I think create or replace can works on two ways:
1) if object exist
            drop it
     create object
2) if object exist
            alter it
     else
            create it
This two have significant different. For example, if I have a function f1 which depends on function f2, then if I use create or replace for function f2, the case 1 will failed because the drop statement will be failed due to dependence checking. Also, no 2 have several advantage, such as the dependence, and permission is not changed, and object id is not changed as well. I personally think it is desirable. However, case 2 can only work with view, function, procedure, trigger, but I think it make sense too. If your guys have any opinion for this, please post it here.
    

One of those cases when a little change would make a huge difference.

This is a good idea.

Though ALTER is misleading. ALTER means to change something about the meta-data. Here the actual PROCEDURE is being replaced. Hence Oracle's CREATE OR REPLACE.

I'd suggest allowing REPLACE on its own, which would implicitly include CREATE. Or change OR to WITH: CREATE WITH REPLACE .

Warning: This workaround is laborious and cumbersome.

IF OBJECT_ID('dbo.<objectname>') IS NOT NULL
DROP <objecttype> dbo.<objectname>
CREATE ...

Second workaround, using trigger as an example (from msdn.com):

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO

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

There is another more elegant workaround that is not too bad, although I agree that a declarative solution such as adding a "WITH Replace_Existing" clause would make more sense...

-- Drop the procedure definition if it currently exists.
IF EXISTS (SELECT * FROM sysobjects
    WHERE ID = OBJECT_ID(N'[dbo].[mySP]')
                 AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[mySP]
GO

CREATE PROCEDURE [dbo].[mySP] AS
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [dbo].[mySP]
@inStartDate DATETIME,
@inEndDate DATETIME
AS
...