There are times when we believe that a procedure does not exist in the database and attempt to create it; however, if the procedure already exists, it returns an error, There is already an object named x in the database.

If we attempt to ALTER a stored procedure that we believe already exists but does not, it returns an error stating that no such object exists.

Our assumptions could be incorrect, and we could encounter an error while attempting to create or modify the stored procedure. To avoid this, SQL Server 2016 (SP1) introduced the CREATE OR ALTER statement via a system update (KB3190548).

After you apply this update, you can only execute the CREATE OR ALTER statement on the following programmability objects:

  • Views
  • Stored procedures (including natively compiled)
  • User-defined functions (including natively compiled)
  • Triggers (including natively compiled)

The CREATE OR ALTER statement does not apply to the following:

  • Objects that require storage (tables, indexes, indexed views, and so on)
  • Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE SCHEMA)
  • Deprecated programmability objects (RULE and DEFAULT)

The permissions are retained when you use CREATE or ALTER.


USE dbStudents
CREATE OR ALTER PROC uspGetStudentsScore
aaaaSELECT TOP 10 * FROM [dbo].[tbStudentsScore] ORDER BY iScore DESC

Hope you find this article helpful.

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s