How to Modify a Stored Procedure in SQL Server?

In this article, we will learn to modify the created stored procedure in MS SQL.You can modify the Stored Procedure in two ways. one is by using a client called SSMS and other way is by using T-SQL statements + SSMS in MS SQL Server.

Method 1: Using SQL Server Management Studio (SSMS) to Modify the Stored Procedure

• In Object Explorer, connect to an Database Engine.

• Expand the database in which the procedure belongs.

• Expand Stored Procedures, right-click the procedure, and then select Modify.

• Modify the stored procedure through the readily available SQL statement.

Method 2: Using T-SQL statements and SSMS.

• In Object Explorer of SSMS, connect to an Database Engine.
• Create a new data base
• Use the Database created
• Open a new Query page using SSMS
• Modify the Procedure using ALTER PROCEDURE statement

Example 1:

In this example, we will use T-SQL to create and modify a Stored Procedure by the name My_procedure in a Database. First Create two pages in SSMS, one for creating Procedure and other for Modifying.

Creating a Procedure:

CREATE DATABASE Sample_DB;
GO

USE Sample_DB;
GO

CREATE PROCEDURE My_Procedure 
    @first_name varchar(20),
    @last_name  varchar(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT @first_name , @last_name;
END
GO

EXEC My_Procedure 'Andy', 'Jessy';

Output : The output shows creation and execution of the above mentioned procedure

Modifying the Created Procedure:

ALTER PROCEDURE [dbo].[My_Procedure] 
    @first_name varchar(20),
    @last_name  varchar(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  @last_name + ', ' + @first_name;
END

Output : Execute the above code and then run the procedure in a separate page

Example 2: 

In this example , we are modifying the same Procedure created in the above example in a different way. Create 3 pages in SSMS , one for creating Procedure , 2nd for Modifying and 3rd for executing modified procedure .

The aim is adding 3 parameters and one case statement to the My_procedure.

Page 1 : Creating Procedure

CREATE PROCEDURE My_Procedure 
    @first_name varchar(20),
    @last_name  varchar(20),
    @choice int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT @first_name , @last_name , @choice;
END
GO

EXEC My_Procedure 'Andy', 'Jessy' , 1;

Page 2 : Modifying Procedure

ALTER PROCEDURE [dbo].[My_Procedure] 
    @first_name varchar(20),
    @last_name  varchar(20),
    @choice int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  'Name' = case @choice
    when 1 then @first_name
    when 2 then @last_name
    ELSE @first_name + ' ' + @last_name
    END
END

Page 3 : Executing the modified procedure

EXEC My_Procedure 'Andy', 'Jessy', 2;

Output :