sql server - i have create a basic store procedure..when i m changing in this and execute it give me error that this name of SP already exit

Question

This is my code

USE AccountSystemTraining
GO

CREATE PROCEDURE dbo.precetics
AS
SELECT * FROM Department where id=1
GO

error:

Msg 2714, Level 16, State 3, Procedure precetics, Line 4 There is already an object named 'precetics' in the database.

Answer 1

If the procedure already exists and you need to change it then you may need to use ALTER instead of CREATE:

ALTER PROCEDURE dbo.precetics AS SELECT * FROM Department where id=1 GO

Note: From SQL Server 2016 you can do

CREATE OR ALTER PROCEDURE dbo.precetics AS SELECT * FROM Department where id=1 GO

which will always work without erroring regardless the procedure exists or not. In previous SQL server you can go around the issue by testing for existence first.