ALTER vs DROP & CREATE in SQL Server

Question

After creating stored procedure in SQL Server why to replace Create with Alter? What will happen on execution if we do not change it? Is there a better alternate to it other then checking if exist and drop?

Answer 1

Create will fail if table exists. Alter will fail if table does not exist.

If you ask why to use Alter if you can drop and create, a few reasons:

• certain permissions already assigned to the object, you would need to regrant permissions if you drop it
• object is probably used by other objects with schema binding

Answer 2

There is no "CREATE OR REPLACE" syntax in SQL Server (like in other RDBMS)

I tend to use this pattern if I'm unsure that something exists.

IF OBJECT_ID('dbo.MyProc') IS NOT NULL
   DROP PROC dbo.MyProc
GO
CREATE PROC dbo.MyProc
...
GO
GRANT EXECUTE ---
GO

Answer 3

From my learnings,

• Whenever you provide a database build, It is a good practice to check if Stored Procedure exists to drop and then recreate the procedure.

• Changing from create to alter might happen while debugging the procedure but this is not a standard practice while providing build.

Answer 4

On the topic of helpful scripts I prefer to create the procedure if not exists and then alter it instead.

IF (select object_ID('schema.Procedure')) is null
    exec('Create procedure schema.Procedure as select 1')
GO

Alter procedure dpm.TableCellLoad

This way priviligies granted to the procedure will always remain and if the procedure did not exist it is created.