How to Modify a Stored Procedure in SQL Server?
Last Updated :
26 May, 2023
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 :Â
Â
Share your thoughts in the comments
Please Login to comment...