CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Snehal Mane
Snehal Mane

Reputation: 13

Check if stored procedure exists accordingly create or alter

I want to check if a stored procedure already defined in db. If defined I have to execute alter script else create.

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'EmployeeInternalReferenceNumber_Copy')
    BEGIN
        DROP  Procedure [dbo].[EmployeeInternalReferenceNumber_Copy]
    END
 Go
CREATE PROCEDURE [dbo].[EmployeeInternal]

Above script is not working as I do not want to drop proc just alter or create. I am using SP2

Upvotes: 1

Views: 874

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can use drop procedure if exists instead of if exists

DROP PROCEDURE IF EXISTS EmployeeInternalReferenceNumber_Copy;
CREATE PROCEDURE [dbo].[EmployeeInternal]

Or if you want to alter procedure EmployeeInternal then use alter instead of create

ALTER PROCEDURE [dbo].[EmployeeInternal]

Upvotes: 0

Charlieface
Charlieface

Reputation: 72298

In SQL Server version 2016+, you can simply use:

CREATE OR ALTER PROCEDURE [dbo].[EmployeeInternal] 
AS

Upvotes: 2

Related Questions