T-SQL Tuesday #080 : CREATE OR REPLACE

This month's T-SQL Tuesday, the 80th event, is being hosted by Chris Yates (blog | @YatesSQL). Today happens to be his birthday, so he approached the topic with ideas about gifts.

He had several suggestions for our posts, and one that stood out to me aimed at features we wished weren't missing from SQL Server:

Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?

While I feel I may have used up my wish list in last month's T-SQL Tuesday, I could probably handle it if this were the topic every month, because there are a lot of things I'd love to see added to our favorite database platform.

One specific feature request, though (which was re-invigorated when SQL Server 2016 added DROP IF EXISTS), is the undoubtedly more useful CREATE OR REPLACE.

DROP IF EXISTS can be very useful when you just want to write a simple CREATE PROCEDURE, for example, and discard anything that existed before. The way we would do this in the past would be to check for the object's existence, drop it if it existed (which in some cases can require dynamic SQL), and then go ahead and re-create:

IF EXISTS
(
  SELECT 1 
    FROM sys.procedures 
    WHERE [schema_id] = 1 
    AND name = N'MyProcedure'
)
BEGIN
  DROP PROCEDURE dbo.MyProcedure;
END
GO
CREATE PROCEDURE dbo.MyProcedure
AS
  -- ... the good stuff ...

With DROP IF EXISTS, I can now do this slightly less awkwardly, without the IF check and without any need for dynamic SQL:

DROP IF EXISTS PROCEDURE dbo.MyProcedure;
GO
CREATE PROCEDURE dbo.MyProcedure
AS
  -- ... the good stuff ...

DROP IF EXISTS is certainly cleaner, both in logic and in sheer code size. Please note, though, that DROP IF EXISTS can "fail" by hiding a different, permissions-related issue.

Speaking of permissions...

DROP IF EXISTS doesn't solve an underlying issue with all of the above approaches: permissions. If I have set up elaborate permissions on an object, I don't want to just throw that away and create it from scratch. So what do people do now? Well, typically, the opposite of the above, either they script a conditional CREATE or ALTER, which has to be done in dynamic SQL (because CREATE or ALTER PROCEDURE has to be in its own batch):

DECLARE @sql nvarchar(max) = N' PROCEDURE dbo.MyProcedure
AS
  -- ... the good stuff ...';
 
IF EXISTS
(
  SELECT 1 FROM sys.procedures ...
)
BEGIN
  -- let's ALTER so we don't lose permissions
  SET @sql = N'ALTER ' + @sql;
  EXEC sys.sp_executesql @sql;
END
ELSE
BEGIN
  -- doesn't exist, let's create it:
  SET @sql = N'CREATE ' + @sql;
  EXEC sys.sp_executesql @sql;
END

Or something a little more clever: creating an empty shell of a procedure if it doesn't already exist, and skipping directly to the ALTER if it does. This allows the body of the procedure to be modified and maintained in source control without the complications of dynamic SQL:

IF NOT EXISTS
(
  SELECT 1 FROM sys.procedures ...
)
BEGIN
  -- create a shadow of a procedure
  EXEC sys.sp_executesql N'CREATE PROCEDURE dbo.MyProcedure AS PRINT 1;';
END
GO
ALTER PROCEDURE dbo.MyProcedure
AS
  -- ... the good stuff ...

That solves the problem, but it is not very tidy, and it requires that extra scaffolding accompany the procedure body in source control (or wherever you keep your database scripts - because you keep your database scripts somewhere, right? :-)).

What would be the bomb...

Yeah, I've probably dated myself there, but the ultimate solution would be inline syntax similar to DROP IF EXISTS, that creates the procedure if it doesn't already exist, and alters it if it does:

CREATE_OR_ALTER PROCEDURE dbo.MyProcedure
AS
  -- ... the good stuff ...