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.
Example:
USE dbStudents
GO
CREATE OR ALTER PROC uspGetStudentsScore
AS
BEGIN
aaaaSELECT TOP 10 * FROM [dbo].[tbStudentsScore] ORDER BY iScore DESC
END
GO
Hope you find this article helpful.
Big Data and SQL 
One comment